How To Use the SUMIF function in Mac Numbers

The story of the SUMIF function started when I wanted to calculate my spendings on food. But my spending column had all kinds of spendings in it. For example, I had some spendings in the education category and some in the transportation category. The challenge was to filter out only the food spendings and add them up. And Mac Numbers had to do all of those automatically.

01. How do the SUMIF function parameters work in Numbers?

The function help in Mac Numbers provides the definitions for each parameter of the SUMIF function. But they are hard to understand. So let me explain them to you by using the example above. 

Each SUMIF function requires three parameters: test-values, condition, and sum-values. My table has 2 columns. One column is the category. The other column is the spending. I only want to add the spending which has the food category. 

In this case, the test-value is my category column. Basically, Mac Numbers looks at each value in the category column. Then it tests the category value against the condition parameter. The condition parameter in this case is “Food”. If the category value isn’t food, then Mac Numbers ignores the line. If the category value is the food category, then it adds the spending to the total. And the sum-values parameter is the spending column.

Please remember that the test-values and the sum-values must have the same size. That means they have only one column and the same number of lines. Otherwise you will see a syntax error.

The limitation of the SUMIF function is that it can only look at one column for filtering. If I have another column for the month, and I want to total my food spending in January, I cannot do it with the SUMIF function. I have to use the SUMIFS function. 

Anyways, if you are looking for general definitions of those parameters, I write them below.

• Test-values: the column whose values are tested to decide which line is calculated for the sum.
• Condition: the logic to select or ignore a value in the test-value column.
• Sum-values: the column whose values are added to the total if they are selected.

Now let me put the parameters together to calculate my total food spendings. My test-value is the category column which is from cell D4 to cell D10. My condition has only the “Food” value. It tells Numbers to select the line with the food category. And my sum-values is the spending column which is from cell E4 to cell E10.

In my example, I have 2 food spendings. The first food spending amount is 200. The second food spending amount is 100. So the SUMIF function gives me a total amount of 300. 

I hope the article helps you understand the SUMIF function. Leave a comment if you have any questions. I will try to help.