The SUMIF Function in Apple Numbers Explained

The best method to sum a group of numbers with a single criteria is the SUMIF function

If you want to SUM a set of numbers that satisfy a single condition, then you'll want to use the SUMIF function in Apple Numbers. The SUMIF function helps you to SUM a collection of numbers IF they match the criteria you specify. It's a great timesaver and more efficient than trying to filter data. Plus, this function isn't hard to master. While there are a few things to keep in mind, in this post, I'll explain exactly how to use this function in your spreadsheets.

Things to Know

• There are three arguments you should satisfy for the SUMIF function to work. They are the test-values, the condition, and the sum-values.
• One way to wrap your mind around this function is to think of it as a TRUE or FALSE detector. If TRUE, then include this number in the SUM. If FALSE, don't include in the SUM. Hence the name, SUMIF (pronounced "sum if")
• With this function, you are limited to a single condition only. If you have more than a single condition to SUM, you'll want to use the SUMIFS function instead (notice the "s" at the end of IF to represent more than one).

How to Use the SUMIF Function in Apple Numbers

To use the SUMIF function, first confirm you have only a single condition as your criteria. If you have more than one condition to SUM, then it's necessary to use the SUMIFS function. Figuring out which function to use is sometimes trickier than you think. Here's an example:

Many times people can explain what they want to see in the spreadsheet such as, "Show me the total payments by card for the stylist Luna." and try to write a SUMIF function for it. Can you determine if a SUMIF function will work?

The answer is it won't. The issue is there are actually two conditions being asked in that example. The first condition is to SUM the payment methods by card only. The second condition is to show this information just for Luna. The payment method and stylist are two conditions. Now, you can accomplish this type of SUM using a SUMIFS function, but you can't do it with the SUMIF function. Again, only one condition for SUMIF.

How to Write the SUMIF Function

It can be confusing when learning the SUMIF function on what the inputs are. The trickiest part, I think, is understanding what is being asked of the function arguments because our brain doesn't conceptualize in the same way.

The SUMIF function in Apple Numbers has three arguments that include:

• test-values = The collection containing the values to be tested. The test-values can contain any kind of value like text, numbers, etc.
• condition = An expression that compares or tests values and results in an answer of TRUE or FALSE. This means if the condition is if TRUE, include it in the SUM. If FALSE don't include it in the SUM.
• sum-values = An optional collection containing the values to be summed. The sum-values can contain numbers, date/time values, or duration values. It should have the same dimensions as test-values meaning if you have 10 test values, you must also have a collection of 10 sum-values.

An Example Using the SUMIF Function in Apple Numbers

Let's take the following table and put together some common question people want to know. Let's figure out the following:

• Total Payment to Each Stylist
• Total Tips to Each Stylist
• Total Payments by Client
• Total Tips by Client
• Total by Payment Method

In our first question, "Total Payment to Each Stylist", we can use the SUMIF function.

• Our test-values will be Column A of the Completed Appointments table.
• Our condition is "Luna" so we can simply select the cell with her name in Cell A2 of the SUMIF Stylist table.
• Finally, the sum-values are in Column C of the Completed Appointment table as these are the values we want to see totals for Luna.

This gives us the answer of $84. If you spot check it, that total comes from Rows 2 and 7 that are $56 and $28 respectively.

To find the answer to the other questions, simply adjust the criteria used in the formula to match what you want to see. As you can see, the insights are easily and quickly discovered with the SUMIF function. The SUMIF STYLIST table, the SUMIF CLIENT table, and the SUMIF PAYMENT table are setup to answer those all questions.

Why SUMIF Instead of Filtering?

While the SUMIF function is helpful, why couldn't you use a filter instead? You'd still be able to gather the same information, right? Technically yes, but it's much more cumbersome. If we were to use a filter for our above questions, we could only answer a single question at a time. Of course, you could create additional tables with separate filters, but that isn't practical.

Also, filtering wouldn't provide updates like the SUMIF formula does. As you continue adding entries, the SUMIF formula would continue to update assuming the criteria and test & sum areas result in a match. For filtering, you need to un-filter and re-filter to see an updated list.

At the end of the day, there is certainly more than one way to go about something in a spreadsheet. That's what makes them so versatile. However, the best solution often is the efficient and straightforward one. The example above contains 10 entries. Imagine if you had 100 or 1000 entries instead. No way would want to filter and un-filter. That's too much work. It's clear that the SUMIF function is the better solution.

Wrap Up

To summarize, the SUMIF function is a great time saver and is better to use in many cases than using a filter. Use the SUMIF function when you have a single condition and use the SUMIFS function when you have multiple conditions. While this function seems confusing at first, you'll easily master with a little understanding and practice.

FOR MORE INFO

SUMIF in Apple Numbers Documentation