Forum Index
shamskm.com Home
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

The Magic of SUMIFS Function in Excel

 
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum
View previous topic :: View next topic  
Author Message
Admin
Site Admin


Joined: 25 Sep 2007
Posts: 268

PostPosted: Sat Mar 04, 2017 7:50 pm    Post subject: The Magic of SUMIFS Function in Excel Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> Technology & How-To Forum All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group