The Magic of SUMIFS Function in Excel

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

The Magic of SUMIFS Function in Excel

Post by Admin » Sat Mar 04, 2017 7:50 pm

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



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:



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


Read wonderful articles (English & German) on Science & Tech, Environment, Health and many other topics only on BlogArena.

For comments of suggestions, please contact us:

Post Reply