SUMIFS in Google Sheets to sum cells with multiple criteria (AND / OR logic)

SUMIFS in Google Sheets - 3 things to remember

Using SUMIFS formulas in Google spreadsheets is quite easy. To prevent most common errors, just remember these three simple facts:

1. The SUMIFS function adds up only those cells that meet all conditions, i.e. all of the specified criteria are true for a cell. This is commonly referred to as AND logic.
2. Sum range and all criteria ranges should be equally sized, i.e. have the same number of rows and columns, otherwise your SUMIFS formula will throw a #VALUE error.
3. Depending on the nature of your conditions, build your criteria appropriately:
   • Text, wildcard characters and dates should be enclosed in quotation marks like "apples" or "*" or "4/19/2024".
   • Numbers and cell references are not enclosed in quotation marks when used on their own.
   • Logical operators with numbers, dates or text must be enclosed in double quotes like ">200", ">4/19/2024" or "<>apples" .
   • Logical operators used together with cell references and other functions require the quotes to begin a text string and ampersand to concatenate and finish the string off. For example: ">"&B1 or ">="&TODAY().

Okay, that's enough theory, let's build some useful formulas already!

SUMIFS with logical operators

When your conditions are numbers or dates, use the comparison operators to express the criteria:

• equal to (= or omitted)
• not equal to (<>)
• greater than (>)
• less than (<)
• greater than or equal to (>=)
• less than or equal to (<=)

For example, to add up amounts in B2:B13 that are greater than or equal to 200 and delivered before 1-Apr-2024, use this formula:

=SUMIFS(B2:B13,B2:B13,">=200", C2:C13, "<4/1/2024")

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(B2:B13, B2:B13, ">="&F1, C2:C13, "<"&F2)

The screenshot below shows the result:

SUMIFS with other functions

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(B2:B13,A2:A13,F2,C2:C13,"<="&TODAY())

And get the following result:

SUMIFS with blank and non-blank cells

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:

• "=" to sum cells that are completely blank, i.e. contain absolutely nothing.
• "" to sum empty cells including zero length strings.
• "<>" to sum non-blank cells including zero length strings.

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(B2:B13,A2:A13,"apples",C2:C13,"<>")

SUMIF with OR criterion (multiple criteria in the same column)

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 example:

=SUM(ARRAYFORMULA(SUMIF(A2:A13, {"apples", "bananas", "lemons"}, B2:B13)))

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(A2:A13, {E1, E2}, B2:B13)))

Or

=SUM(ARRAYFORMULA(SUMIF(A2:A13, E1:E2, B2:B13)))

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((A2:A13="apples") + (A2:A13="bananas"), B2:B13)

For multiple OR criteria (three or more items), you'd better use this syntax:

=SUMPRODUCT((A2:A13={"apples", "bananas"}) * B2:B13)

Replace the array elements with cells references, and you will get the most compact formula to sum cells with multiple OR criteria ever!

=SUMPRODUCT((A2:A13={E1, E2}) * B2:B13)

The screenshot below shows the result:

Four different formulas, the same result. Which one to use is the matter of your personal preference :)

SUMIF with OR criterion and results in different cells

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(A2:A13, {"apples", "bananas", "lemons"}, B2: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 (D2 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, the result won't be expanded and the formula will turn into an error.

If you'd rather have subtotals in a column, then separate the array elements with semicolons to make a vertical array:

=ARRAYFORMULA(SUMIF(A2:A13, {"apples"; "bananas"; "lemons"}, B2:B13))

And the results will be output vertically in a column:

SUMIFS with OR criteria (multiple criteria in different columns)

This example shows how to sum numbers with several sets of conditions determined by the following logic.

• Within each set, all conditions must be true (AND logic)
• A cell is summed if any set of conditions is true (OR 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(B2:B13, A2:A13, "apples", C2:C13, "16-Mar-2024") +
SUMIFS(B2:B13, A2:A13, "oranges", C2:C13, "16-Mar-2024")

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 F2 and items (OR criteria) in contiguous cells, say F1 and G1. Now, you can use the following formula to sum numbers in B2:B13 based on the above criteria:

=(SUMPRODUCT(--(C2:C13=F2), (--(ISNUMBER(MATCH(A2:A13, F1:G1, 0)))), B2:B13))