The SUMIF Function in Apple Explained

The best method to sum a set of numbers using a single criteria

Let's say you want to generate a total, but only sum the numbers if they're related to a specific item. That's when you'll want to use the SUMIF function. The SUMIF function (literally "sum if") will sum a collection of numbers if they match the criteria you specify. It's a huge timesaver and a great way to ensure you're not overlooking an entry. If you're summing specific numbers by hand or using filters/sorting to accomplish this, you'll definitely want to learn about the SUMIF function and why it's a more reliable method to use.

Things to Know

• There are three arguments in the SUMIF: They are test-values, condition, and sum-values.
• With this function, you are limited to a single condition only. That means you can't sum numbers that have x and y attributes. However, if you have more than a single condition required to sum, then use the SUMIFS function instead (notice the "s" at the end of IF to represent more than one condition).

What is the SUMIF function?

The SUMIF function allows you to total a set of numbers within a larger group of numbers using a single, identifying criteria.

For example, let's say you have a concession stand and you want total drink sales, but only for the total sales that are Pepsi, not Coke or 7-Up, etc. The SUMIF function can help you do this easily. First, you'd select all the entires, but within the function indicate that you only want the sum for those entires associated with "Pepsi". That's what the SUMIF function does.

How to Use the SUMIF Function in 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