How To Use SUMIFS For Multi Criteria In Mac Numbers

Quite often I get the requirement to calculate the sum of a few table rows which are selected based on multiple criteria. There are two solutions to this problem. 

One solution is to use the filter feature of Mac Numbers. I add the filter to each column and filter out the lines that I want. Then I add those lines together using the SUM function. This approach is intuitive. But it requires a lot of manual steps.

The second solution is to use the SUMIFS function of Numbers. The SUMIFS function can look at multiple columns of the table, apply a filter condition to each column, select the rows, and sum the selected rows. This approach looks complex. But it simplifies my spreadsheet.  

01. How does the SUMIFS function work in Numbers?

Let’s say you have a table of students. There is an income column to tell you how much money each student makes. There is an age column to tell you how old each student is. And you want to sum the incomes of the students who are 15 years old. The table is below.

If you use the SUMIFS function, the sum-values parameter is the income column that you want to sum up. The test-values parameter is the age column that you want to filter out. The condition parameter is the older-than-15-year-old comparison. 

I hope the example above explains the SUMIFS parameters well. I summarize the definitions of those parameters below.

• Sum-values: A collection containing the values to be summed.
• Test-values: A collection containing values to be tested. 
• Condition: An expression that compares the test-values to other values. 

If a test-value satisfies the condition, then SUMIFS function includes the line to its calculation. In my example, only Rob and Helen are 15 years old. And the SUMIFS shows their total income of 3000. 2000 comes from Rob. And 1000 comes from Helen.

02. How to add multiple criteria to SUMIFS function

The difference between SUMIFS function and SUMIF function is that you can have multiple pairs of the test-value parameter and the condition parameter in the SUMIFS function. 

For example, I add another column for the states where the students come from. And I want to sum the incomes of the students from NJ only. Then my test-values parameter is the state column and my condition parameter is the equal-NJ comparison. And I add this pair after the first pair to select the ages. 

So the rule is to create a pair of test-values and condition parameters for each criterion in your list. In my example, only Rob is 15 years old and from NJ. So only Rob’s income is included in the SUMIFS calculation. And the result is 2000.