The Magic of SUMIFS Function in Excel

Forum to talk about all the latest technologies - ins and outs!!
Post Reply
Admin
Site Admin
Posts: 325
Joined: Tue Sep 25, 2007 12:41 pm

The Magic of SUMIFS Function in Excel

Post by Admin »

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")



Image



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"))



Image



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.

Have fun :-)
Post Reply