Sum Numbers Using Criteria in Apple Numbers

When you want totals that reflect some type of attribute, don't use a filter. Instead, use the SUMIF or SUMIFS function to tell the spreadsheet to SUM these numbers IF it matches condition X

If you want to SUM a set of numbers that match certain criteria, learn the SUMIF and SUMIFS functions. These functions are great timesavers. I admit that I still get a little giddy when I use these functions as they simply do what I specify. Poof, the spreadsheet provides the correct answer! The key to these functions are the setup. If you find yourself struggling, this post will help fix that. Whatever criteria you have, I'll show you how to SUM using criteria in Apple Numbers.

Things to Know

• To SUM a collection of numbers that match a single condition, use the SUMIF function. If you have more than a single condition or test-values, use the SUMIFS function instead.
• The condition(s) may be specified within the function itself (although I would recommend the condition is a reference from a cell within the spreadsheet). The sum-values and test-values come from cells within the spreadsheet.
• While the inputs for the SUMIF and SUMIFS functions are the same, the order for each input is different.Keep this in mind when you go to use them.

Functions That SUM Using Criteria in Apple Numbers

If you've never heard of the SUMIF or SUMIFS functions, now is a great time to learn them. The SUMIF and SUMIFS (read as "Sum If(s)") are a melding of sorts between the SUM function and the IF function. Similar to the SUM function because it can calculate a total for all selected cells in a column or row. Similar to the IF function because it allows you to add a condition to what you want to SUM. If a condition matches within a group of numbers, the SUMIF and SUMIFS functions will parse the SUM for you!

The Difference Between the SUMIF and SUMIFS Functions

Both functions can sum using criteria in Apple Numbers. However, the slight differences between the two are this:

1. SUMIF allows you to SUM using only a single condition and a single set of test-values. SUMIFS allows you to SUM using multiple conditions and test-values.
2. The order of the arguments for each function is slightly different.
   • For SUMIF, the order is (test-values, condition, sum-values)
   • FOR SUMIFS, the order is (sum-values, test-values, condition, test-values…, condition…)

How to Use the SUMIF Function

Let's say you have a list of invoices you want to total, but only SUM those invoice that are paid. To SUM using criteria in Apple Numbers, we can use the SUMIF function to calculate this only the paid invoices.

The SUMIF function comes with 3 inputs:

• Test-values: The collection containing the values to be tested. Test-values can contain ANY value.
• Condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. Condition can include comparison operators, constants, the ampersand concatenation operator, references, and wildcards (This is all to say your condition isn't limited to only certain kinds. You have options!).
• Sum-values: An optional collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values. It should have the same dimensions as test-values. (Meaning if you have 10 test-values, you need to have 10 corresponding sum-values).

In the screenshot below, we can define the inputs as follows:

• Our test-values - We want to SUM the check boxes that have a checkmark in Column A. These are the test-valuesbecause our SUM will depend if these boxes are checked or not.
• The condition - We enter "true" because we want to SUM when the corresponding cell in Column A contains a checkmark (in Numbers, checkboxes are essentially a visual way to show true or false. When checked, it's true. When unchecked, it's false).
• Sum-values - Column B. The values in this column produce the SUM when the box in Column A has a checkmark.

A Few Things to Note

It may not be obvious, so there are a few things I want you to note when you're using this function.

1. The columns for test-values and sum-values don't need to be next to one another or even on the same table or tab.
2. The quantity of test-values and the quantity of sum-values needs to be the same. Otherwise, the function won't work.
3. The result from the function updates in real time. If a box were to become checked, the total will change.

For brains like mind, it's easier for me to remember the function inputs this way:

1. What's column is the condition we're testing?
2. What is that specific condition?
3. If the condition matches, what column do we sum?

How to Use the SUMIFS Function

The SUMIFS function also comes with a minimum of 3 inputs, but in a slightly different order:

• sum-values: A collection containing the values to be summed. sum-values can contain number values, date/time values, or duration values.
• test-values: A collection containing values to be tested. test-values can contain any value.
• condition: An expression that compares or tests values and results in the boolean value TRUE or FALSE. conditioncan include comparison operators, constants, the ampersand concatenation operator, references, and wildcards (Again, similar to the SUMIF function, you have options!).
• test-values…: Optionally include one or more additional collections containing values to be tested. Each test-values collection must be followed immediately by a condition expression. This pattern of test-values, conditioncan be repeated as many times as needed.
• condition…: If an optional collection of test-values is included, condition… is an additional expression that results in a boolean value TRUE or FALSE. There must be one condition following each test-values collection; therefore, this function always has an odd number of arguments.

Similar to the SUMIF function, you have the same inputs. However, notice the order is different. First you enter the sum-values, then the test-values, then the condition. Why the different order? My guess is to make the formula flow easier.

With the SUMIFS function, you're indicating you want to SUM numbers that match multiple criteria. Let's take a look at the example below.

An Example of the SUMIFS Function

We have the same spreadsheet as before, except an additional column called "Location". Let's say we want to SUM invoices that have a checkmark in Column A AND are located in "Australia". Using the SUMIFS function, we can specify:

• What we want summed, which is column C.
• Then we specify what column will be the test column, which is column A.
• We'll then say that the condition for those in column A will be "TRUE"
• Next, we'll add another test column, column B.
• Finally, the condition will be Australia so we can type in quotes "Australia" which will have the spreadsheet search for a match to that.

Keep in mind, ALL conditions must match for the SUMIFS function to return a value. In our example, the proper SUM would be $20. Only 2 of the 3 that match with Australia AND paid are those in cells B5 and B6. While cell B2 is located in Australia, the checkmark is not there. 

So, you can SUM using criteria in Apple Numbers on as many items as you need with the SUMIFS function.

Key Takeaways to SUM Using Criteria in Apple Numbers

The SUMIF and SUMIFS function can be very powerful as they can total only those that match whatever conditions you dictate in your spreadsheet. To maximize the functions, you should keep in mind the following:

• While you can use SUMIF and SUMIFS for data contained within a row or column, I find it much easier to have your data listed in columns instead of rows.
• SUMIF and SUMIFS functions will return the sum of those test-values and conditions that correspond in the same row (or columns in listed horizontally)
• SUMIF will allow only 1 column for test-values and 1 column for conditions while SUMIFS will allow more than 1
• For SUMIFS, ALL conditions much match in order to sum those total cells

SEE ALSO

SUMIF Function from Apple Documentation

SUMIFS Function from Apple Documentation