The SUMIF Function in Apple Explained

Overview of the SUMIF Function in Numbers

Let's say you want to generate a total, but only sum the values that are related to a specific cell criteria. The SUMIF function can help with this.

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.

What It Does

• The SUMIF function will give you the total sum of numbers with a group of cell if they match a single condition you specify
• Like many IF-based functions, SUMIF uses boolean logic (true or false). If the criteria specified within the function statement is true, SUMIF will count that cell. If it's false, it won't include it.
• SUMIF uses a single condition against the sum-values (the collection of cells you're looking to sum that match your criteria). If you have more than one condition, you must use the SUMIFS function.

Function Overview

Purpose: Returns the sum of cells in a collection that satisfy a given condition
Result: A sum of the cells that match the condition
Arguments: test-values, condition, sum-values (optional)

• test-values: the entire collection of cells you'll test
• condition: the criteria to use on whether or not the function should count the cell. The condition is written as an expression. From this expression, it'll compare or test the values in the test-array with results determined as true or false. This means if your condition comes back as true for that cell, the function will count that cell. If false, it won't count that cell. Your criteria for the condition can be expressed in several ways
  • comparison operators (=, <, >, <=, >=, <>)
  • constants (values that don't change)
  • references (a value from another cell)
  • ampersand concatenation operator (combine the contents of conditions or cells)
  • wildcards (?*~) or REGEX
• sum-values (optional): the collection of values you want to sum. This is an optional input and if not used the function will default to the test-values.

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.

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.

SUMIF Considerations and Limitations

These are some consideration and limitations when using the SUMIF function.

Considerations

• When using the optional sum-value argument, this must have the same dimensions as your test-values. For example, if your test-values are 10 rows long, then your sum-value must also be 10 rows long

Limitations

• SUMIF can only use a single condition. If you have more than one condition you need to match, use the SUMIFS function instead

Why Use the SUMIF Function Instead of Filtering or Sorting?

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.

Additional Function References

SUMIF - Apple Numbers Documentation

AVERAGEIF
AVERAGESIF
SUMIFS
SUM