*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.

## Google Sheets SUMIFS function - syntax & basic uses

The SUMIFS function is Google Sheets is designed to sum numbers with multiple conditions. Its arguments are as follows:

Where:

**Sum_range**(required) - the range to be summed.**Criteria_range1**(required) - the range of cells to be evaluated by*criterion1*.**Criterion1**(required) - the condition to be met by cells in*criteria_range1*.**Criteria_range2, criterion2, …**(optional) - additional ranges and criteria to test.

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

- 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. - 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. - 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!

## How to sum with multiple criteria (AND logic)

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((A2:A13="apples")*(C2:C13="delivered")*(B2:B13)))`

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:

- Column A should contain "apples". Assuming we have data in rows 2 to 13, our
*criteria_range1*is A2:A13, and*criterion1*is "apples". - Column C should contain "delivered". Meaning, our
*criteria_range2*is C2:C13, and*criterion2*is "delivered". - Numbers to sum are in column B, so our
*sum_range*is B2:B13.

Putting the arguments together, we get this simple formula:

`=SUMIFS(B2:B13, A2:A13, "apples", C2:C13, "delivered")`

To add more flexibility, you can input both criteria in separate cells, say F1 and F2, and refer to those cells:

`=SUMIFS(B2:B13, A2:A13, F1, C2:C13, F2)`

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.

### 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,"<>")`

## How to sum with multiple criteria (OR logic)

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.

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

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:A13, D1:D3, B2:B13))`

### 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))`

#### How this formula works

Working from the inside out, here's what you do:

- Check if an item is "apple" or "orange":

`ISNUMBER(MATCH(A2:A13, F1:G1,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).

- Compare a list of dates with the target date: C2:C13=F2
Instead of a cell reference, you can enter the date directly in a formula by using DATEVALUE or DATE function. For example, C2:C13=DATEVALUE("3/16/2024") or C2:C13=DATE(2024,3,16)

In this formula, however, you cannot compare a range with a date directly, like C2:C13="3/16/2024" or C2:C13="16-Mar-2024", 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.

- Use the double unary operator (--) to coerce both arrays of TRUE and FALSE to 1 and 0, respectively.
- Supply the range to sum: B2:B13

SUMPRODUCT will first multiply the elements of 3 arrays (two arrays of 0's and 1's and an array of numbers in B2:B13), 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!

## Spreadsheet with formula examples

SUMIFS Google Sheets Examples (make yourself a copy)

## 181 comments

I want to get the total amount in Column D in Tab1 sheet on basis of multiple criteria that needs to be searched in multiple columns in data tab. attaching a example sheet. The formula should be consistent formula that should work. I wanted to create a dynamic range that is creating formula by searching the search key in role column and preparing a range which then act as a criterion range to SUMIFS formula. I tried various methods such as :

Match+index + Indirect formula to generate that manual automatic range but its not working

https://docs.google.com/spreadsheets/d/1-dDR3Ve9pk4so_JH5wN8NNUIfrzY361ZivdnQFqVWQI/edit?gid=0#gid=0

Could you please help with a formula in Column D "Total Sales" of tab1 that remains consistent in all cells ?

Hello Vidhey,

For me to be able to help you, please clarify if you want to sum the corresponding numbers or just fetch the first matching record. I'm asking because your 'Expected result' column features the first occurrence for 'ABC01, Account Executive' but then some strange number for 'ABC09, Sales Manager' and then 0 for ABC04, Account Executive even though there are matches.

The formula for 'SUMIFS with OR criteria (multiple criteria in different columns)' just saved me. You rock. Thank you!!!

We're pleased Svetlana's article has helped you! ðŸ˜Š

Hello,

I am trying to build a formula but I'm not quite sure how to do it.

I want to automatically add up my expenses based on whether or not a checkbox is checked, but I also want to filter the criteria to a specific month.

My current (working) formula is =sumif(ARRAYFORMULA(TEXT(A3:A,"MMM-YYYY")),N7,G3:G) which gets me all of the numbers for the whole month (expenses and income) but I have 2 columns of checkboxes (Column I for income and Column J for expenses) that I want to add another criteria of if the expenses checkbox column is checked being the only cells that are being summed. But I also want them to be summed up based on the date range input from the formula above. I want to place these formulas in different boxes based on the date so I can compare one months expenses to another months expenses.

Is this possible?

Thank you.

Hello Ben,

For me to be able to assist you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives us a better understanding than any text description.

I kindly ask you to shorten the tables to 10-20 rows.

Note.We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.I'll look into your task and try to help.