by Svetlana Cheusheva, updated on
The tutorial shows how to use the SUMIFS function in Google Spreadsheets to sum cells with multiple criteria. You will find a handful of real-life SUMIFS formula examples with AND as wells as OR criteria.
Google Spreadsheets provide two functions to conditionally sum cells, SUMIF and SUMIFS. Regular visitors of our blog are already familiar with SUMIF that we covered last week, and today we are going to have a closer look at its plural version.
The difference between SUMIF and SUMIFS is as follows:
SUMIF can add up cells based on just one condition. If it is what you want, please check out this tutorial: SUMIF in Google Sheets.
SUMIFS can sum cells based on two or more conditions. Further on in this tutorial, you fill find a simple explanation of SUMIFS syntax and formula examples for different data types.
The SUMIFS function is Google Sheets is designed to sum numbers with multiple conditions. Its arguments are as follows:
Where:
Using SUMIFS formulas in Google spreadsheets is quite easy. To prevent most common errors, just remember these three simple facts:
Okay, that's enough theory, let's build some useful formulas already!
When it comes to summing cells with multiple criteria, you may find a lot of different suggestions in Google groups and other user communities.
Let's say, you have a list of items in column A, amounts in column B, and delivery status in column C. Your goal is to get a total for a specific item with a certain delivery status, for example "apples" that are "delivered". How would you approach the task?
You may be surprised to know that many people still use array formulas for this purpose. For example, this one:
=ARRAYFORMULA(SUMPRODUCT((A6:A14="apples")*(C6:C14="delivered")*(B6:B14)))
What's wrong with this formula, you may ask me. Nothing except that it's excessively complex, difficult to understand and debug. It's like using a sledgehammer to crack a nut. Google Sheets SUMIFS function provides an easier way!
So, let's go ahead and define our arguments:
Putting the arguments together, we get this simple formula:
=SUMIFS(B6:B14, A6:A14, "apples", C6:C14,"delivered")
To add more flexibility, you can input both criteria in separate cells, say B1 and B2, and refer to those cells:
=SUMIFS(B6:B14, A6:A14, B1, C6:C14, B2)
As shown in the screenshot below, our SUMIFS formula works perfectly!
Taking the example further, let's see how to use SUMIFS with other criteria types.
When your conditions are numbers or dates, use the comparison operators to express the criteria:
For example, to add up amounts in B6:B14 that are greater than or equal to 200 and delivered before 1-Apr-2018, use this formula:
=SUMIFS(B6:B14,B6:B14,">=200", C6:C14, "<4/1/2018")
In case you want to replace actual values with references to cells holding your criteria, please remember to enclose the logical operator in quotation marks and to concatenate the cell reference by using an ampersand:
=SUMIFS(B6:B14,B6:B14,">="&B1, C6:C14, "<"&B2)
The screenshot below shows the result:
In some situations, the conditions may depend on the results returned by some other functions. In this case, embed those functions in the criterion arguments of your SUMIFS formula.
As an example, let's sum "apples" that are "delivered". In other words, the delivery date is up to and including today, so we concatenate the "less than or equal to" operator (<=) with the TODAY() function:
=SUMIFS(B6:B14,A6:A14, B2,C6:C14, "<="&TODAY())
And get the following result:
To sum numbers in one column depending on whether a cell in another column is empty or not empty, use one of the following criteria:
In case the Delivery date column contains some gaps, you may want to sum only those "apples" for which the delivery date is set, i.e. a cell in column C is not empty. The task can be accomplished with this formula:
=SUMIFS(B6:B14,A6:A14,"apples" ,C6:C14, "<>")
As you already know, by default, Google Sheets SUMIFS function works with AND logic - all conditions must match to be summed. In some situations, however, you may need to conditionally sum cells with OR logic, when any of the specified criteria is true. Below you will find a few possible ways to do this.
To sum cells with only a couple of criteria in the same column, you can simply add two SUMIF functions together.
For example, to sum the amounts in column B if column A is either "apples" or "bananas", use the SUMIF() + SUMIF() combination in its simplest form:
=SUMIF(A:A,"apples",B:B) + SUMIF(A:A,"bananas",B:B)
If you have three or more criteria, you may be looking for a more compact formula. For this, include the items in an array constant (aka inline array), use ArrayFormula to get a subtotal for each item, and wrap the whole construction into SUM() to add the subtotals together. For eample:
=SUM(ARRAYFORMULA(SUMIF(A6:A14, {"apples", "bananas", "lemons"}, B6:B14)))
Instead of "hard-coding" the items in an array constant, you can enter them in individual cells, and include cell references in the array (in case of non-contiguous cells) or supply a range (in case of contiguous cells).
To better show you that our SUMIF formula works in exact accordance with the specified OR criteria, I've narrowed down the list to two items:
=SUM(ARRAYFORMULA(SUMIF(A6:A14, {B1, B2}, B6:B14)))
Or
=SUM(ARRAYFORMULA(SUMIF(A6:A14, B1:B2, B6:B14)))
Instead of ArrayFormula, you can add up subtotals with the SUMPRODUCT function. For a short list of items, you can put it this way:
=SUMPRODUCT((A6:A14="apples") + (A6:A14="bananas"), B6:B14)
For multiple OR criteria (three or more items), you'd better use this syntax:
=SUMPRODUCT((A8:A16={"apples", "bananas"}) * B8:B16)
Replace the array elements with cells references, and you will get the most compact formula to sum cells with multiple OR criteria ever!
=SUMPRODUCT((A8:A16={B1, B2}) * B8:B16)
The screenshot below shows the result:
Four different formulas, the same result. Which one to use is the matter of your personal preference :)
If you'd like to have a subtotal for each item in a separate cell, take an array SUMIF formula discussed above, adjust the references, and cut off the SUM() part:
=ARRAYFORMULA(SUMIF(A5:A13, {"apples", "bananas", "lemons"}, B5:B13))
This will give you a sum for each item in a separate cell as shown in the screenshot below:
For the sake of clarity, you enter the formula only in the leftmost cell (A2 in this example), and Google Sheets will put the results into as many cells as many items there are in your array constant. Just make sure you have enough empty cells to the right, otherwise some of your data may be overwritten.
If you'd rather have subtotals in a column, then separate the array elements with semicolons to make a vertical array:
=ARRAYFORMULA(SUMIF(A2:A10, {"apples"; "bananas"; "lemons"}, B2:B10))
And the results will be output vertically in a column:
Tip. As usual, you can make your formula more flexible by replacing a hard-coded array with a range reference. That way, your users could type any items in the predefined cells, and you won't have to worry about updating your formula. For example, the formula shown in the screenshot above, can take the following shape:
=ARRAYFORMULA(SUMIF(A2:A10, D1:D3, B2:B10))
This example shows how to sum numbers with several sets of conditions determined by the following logic.
To make things easier to understand, please consider the following example. In our sample data set, supposing you want to sum amounts in column B if column A contains either "apples" OR "oranges" AND the delivery date in column C is "16-Mar-2018".
The most obvious way is to make two SUMIFS formulas to sum "apples" and "oranges" separately, and then add up the results:
=SUMIFS(B6:B14, A6:A14, "apples", C6:C14, "16-Mar-2018") +
SUMIFS(B6:B14, A6:A14, "oranges", C6:C14, "16-Mar-2018")
Or, you can enter your criteria in some cells, as shown in the screenshot below:
Regrettably, Google Sheets do not allow expressing multiple OR conditions using array constants, therefore we cannot make a plural version of our SUMIF with OR criteria formula. Luckily, there is another way to achieve the same result with a bit shorter formula - the SUPRODUCT function.
As an example, let's enter the desired delivery date (AND criterion) in B2 and items (OR criteria) in contiguous cells, say B1 and C1. Now, you can use the following formula to sum numbers in B6:B14 based on the above criteria:
=(SUMPRODUCT(--(C6:C14=B2), (--(ISNUMBER(MATCH(A6:A14, B1:C1, 0)))), B6:B14))
Working from the inside out, here's what you do:
ISNUMBER(MATCH(A6:A14, B1:C1,0))
As the result, you'll get an array or TRUE and FALSE values (TRUE if any of the specified criteria is met, FALSE if none of the criteria is met).
Instead of a cell reference, you can enter the date directly in a formula by using DATEVALUE or DATE function. For example, C6:C14=DATEVALUE("3/16/2018") or C6:C14=DATE(2018,3,16)
In this formula, however, you cannot compare a range with a date directly, like C6:C14="3/16/2018" or C6:C14="16-Mar-2018", because you'd be comparing a cell with a date to a text string. Google Sheets won't understand that and you will most likely get a zero result.
If done correctly, this comparison will give you another array or TRUE and FALSE.
SUMPRODUCT will first multiply the elements of 3 arrays (two arrays of 0's and 1's and an array of numbers in B6:B14), and then sum the products. Because multiplying by 0 always yields 0, only the cells that have 1 in the first two arrays will "survive". As the result, only the amounts with the specified delivery date and item names will be summed.
This is how you use SUMIFS in Google Sheets to sum cells with multiple conditions. To have a closer look at the formulas discussed in this tutorial, I invite you to open our SUMIFS Google Sheets Examples. I thank you for reading and hope to see you on our blog next week!
Table of contents