Chatting about Excel and More

SUMIFS

Excel  2007 and Excel /2010 offers some new functions that go beyond the simple SumIf and CountIF as they allow you to test up to 127 conditions. The new functions are Sumifs, CountIfs and AverageIfs. (Excel 2003 does not offer AverageIf but Excel 2007 does). They are called the Plurals due to the S at the end of the function.

They all work the same way and the syntax is similar. We are going to add up the Sales for Pam's Tea Parlor if the Country of Origin is China  and the Quantity purchased is greater than or equal to 3.   Only 2 rows meet all the criteria and I have highlighted them in purple so they were easy for you  to find.

Here is the formula: =SUMIFS(G7:G21,A7:A21,A7,C7:C21,"China",F7:F21,">=3")

Let's walk through the steps:
SYNTAX
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Sum_range
The sum range is G7.G21
This is the range of cells that you want to do the math on. In our case we want to sum up the Sales column.

Criteria_range1 is A7.A21
This is  first range is the criteria that everything else is based off of. In our example, we are filtering our data  based upon  Column A where the customers are.

Criteria1  is "Pam's Tea Parlor" or A7. 
This is the criteria that Excel is going to look for in the Criteria_Range1 which in our case is Column A.
I used A7 instead of typing "Pam's Tea Parlor" since it saves me worrying about typos and I was not copying this formula so I didn't need to worry about relative and absolute cell references. If you do type in text then you need quotation marks.

So far, we have told Excel to sum the sales if the Customer is Pam's Tea Parlor.
The criteria1  selection  must match something in Criteria_range1  and the criteria2 selection must match something in the Criteria_range2 selection etc.  All further conditions step off Criteria1.

The remaining sytnax is optional and you can continue adding up to 127 criteria. We are only going to add two more- Origin of China and Quantity greater than or equal to 3.

Criteria_range2 is C7.C21
We area filtering our data further and limiting our interest to teas from specific locations that have been ordered by Pam's Tea Parlor

Criteria2 is "China"
Criteria2 must match an item in the column that was specified in Criteria_range 2. We are only looking for teas from China that were ordered by Pam's Tea Parlor. Again, text needs to be in quotations.

Criteria_range3 is F7.F21
This is telling Excel that our next interest is Column F - Quantity

Criteria3 is >=3
Quantity greater than or equal to 3. If you do not put quotations around >=3, Excel will generally add them in even though it seems incorrect. The syntax on SUMIF and SUMIFS is not the same as an IF function.

However please be aware that I could only test for one criteria per column unless you can use a wild card to meet your criteria.

In other words,  I can only test for one country of origin unless I can use a wild card. Excel would not provide an answer if  I had wanted to sum sales for Pam's Tea Shop if the tea came from China and Korea and the quantity was greater than or equal to 3.  I could only select a single country of origin.