Author Message

Joined: 25 Sep 2007
Posts: 283

 Posted: Sat Mar 04, 2017 7:50 pm    Post subject: The Magic of SUMIFS Function in Excel First, what's the difference between SUMIF and SUMIFS functions? The SUMIF function accepts only one criteria or conditon at a time, while as the name says, SUMIFS function accepts multiple conditions or criteria. So the SUMIFS function offers more flexibilty for data analysis. The syntax of SUMIF function: =SUMIF(range, criteria, [sum_range]) The syntax for SUMIFS function: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) sum_range is a required field. It specifies the range of cells that are to be added up. criteria_range1 is the first range of cells where the first criteria (criteria1) is to be evaluated. It's a required field. criteria1 is the condition that is to be evaluated in the criteria_range1. It's a required field. So how it works? The condition you put as criteria1 is searched within the range of cells given as criteria_range1 and if found, then their corresponding values are summed up from the sum_range. criteria_range2, criteria2 are the next range of cells with the respective criteria or conditions. These are optional. The SUMIFS function can take upto a total of 127 range/criteria pairs. Let's try the following example: In the following table, I have data of sales agents, items they sell and pieces they sold. First, I want to calculate total pieces of iPhone sold by sales agent Adam? So in cell C16 enter =SUMIFS(C2:C13;B2:B13;"iPhone";A2:A13;"Adam") Result: 440 Here, C2:C13 is my sum_range B2:B13 is my criteria_range1 "iPhone" is my criteria1 or my first condition A2:A13 is my criteria_range2 "Adam" is my criteria2 or my second condition Now, let's say I would like to calculate total peices of iPhone + Samsung sold by Adam. For this I will use the SUMIFS function within the SUM function. So let's enter in cell C18: =SUM(SUMIFS(C2:C13;B2:B13;{"iPhone";"Samsung"};A2:A13;"Adam")) Result: 650 Explanation is as above. The only thing is, here criteria1 contains two text strings. They are within the curly brackets and separated by semi-colon – {"iPhone";"Samsung"}. Thus if Adam and Tina would be your criteria2, then type – {"Adam";"Tina"} This way, you could have multiple text strings in each criteria. Also read: SUMIF Function in Excel *Recommended: Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena. For comments of suggestions, please contact us: info@shamskm.com
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT + 2 Hours Page 1 of 1

 Jump to: Select a forum Forum Rules----------------General Rules Learn German Easily----------------German as a Foreign Language (Deutsch als Fremdsprache) Category----------------General ForumPolitics - commentsCryptocurrency Market PredictionTechnology & How-To ForumJokesScienceEnvironmental Issues forumGeographic Information System (GIS) forumWebsites Related ForumPublic Health & Yoga forum Cappuccino & Espresso----------------Cappuccino & Espresso Job Posting----------------Job offerThe Space travellers
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum