This tutorial covers everything you need to master the SUMIFS function in Google Sheets for conditional sum. In addition to its standard usage, we will explore how to utilize SUMIFS with wildcards, regular expressions (regex), lambda functions, and nested functions.

The SUMIFS function allows you to conditionally sum a column, making it a powerful tool for summarizing your data, even though QUERY is available as a more advanced option.

We will begin with the basics and guide you through advanced tips to enhance your proficiency in Google Sheets using SUMIFS. When necessary, I will include detailed supporting tutorials to provide further clarification.

Understanding the Basics of SUMIFS in Google Sheets

We typically use either the SUM function or the SUBTOTAL function to calculate the total of a column.

For instance, you can use these functions to compute the total sales in a column. However, what if you need to calculate the total sales in a specific month from a particular region?

This is where the SUMIFS function in Google Sheets comes into play. While other functions like SUM combined with FILTER, SUMPRODUCT, or QUERY can also achieve this, SUMIFS is the specific function designed for this purpose.

Syntax and Usage: Crafting Your SUMIFS Formula

Syntax of the SUMIFS Function in Google Sheets:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

• sum_range: This is the range that will be summed. It should be of a numeric data type.
• criteria_range1: The range against which the first criterion (criterion1) is evaluated.
• criterion1: The first criterion.
• [criteria_range2, criterion2, …]: Optional. Additional ranges and criteria to be examined.

Note: In SUMIF, the sum_range is the last argument. Be cautious not to accidentally place the sum_range as the last argument in SUMIFS.

Filtering Data: Using Multiple Criteria in SUMIFS

The following sample data contains sales data structured with Date, Region, Product, Customer, and Sales in columns A, B, C, D, and E, respectively.

Sample Sheet

You can make a copy of my sample sheet by clicking on the blue button above.

The first column contains dates, and the second, third, and fourth columns contain text, while the fifth column contains numerical values.

Let’s utilize this data to experiment with SUMIFS by applying various criteria and conditions to calculate different sums.

Sample Data (Screenshot):

Problem: Sum the total sales of the product “A” in the North region.

You can use the following SUMIFS formula to obtain the sales total as described above:

=SUMIFS(E5:E, B5:B, B1, C5:C, C1) // returns 270

Where:

• E5:E: This is the range of values (Sales) you want to sum.
• B5:B: This is the first criteria range, which represents the “Region” column. In this case, you are specifying that you want to sum values where the region matches “North.”
• B1: This is the cell reference to the value “North” (or the criterion for the “Region” you’re looking for).
• C5:C: This is the second criteria range, which corresponds to the “Product” column. You’re specifying that you want to sum values where the product is “A.”
• C1: This is the cell reference to the value “A” (or the criterion for the “Product” you’re looking for).

If you need to hardcode the criteria, then use the following formula:

=SUMIFS(E5:E, B5:B, "North", C5:C,"A") // returns 270

These formulas calculate the total sales amount for the product “A” in the North region based on the specified criteria.

Criteria Types

In the above example of the SUMIFS function in Google Sheets, we’ve used two text criteria. But what about numbers, dates, and timestamps as criteria?

The issue will only arise when you hardcode criteria within SUMIFS. Here is an example:

=SUMIFS(E5:E,D5:D, "Customer1", A5:A, DATE(2023, 9, 21)) // returns 300

The formula above returns the total sales amount of “Customer1” on 21/09/2023. You should specify the text criterion within double quotes and the date criterion using the DATE function.

For in-depth details, please check out this: How to Use Different Criteria in the SUMIFS Function in Google Sheets.

Advanced SUMIFS Techniques: Nested Functions and Wildcards

In the SUMIFS function, we cannot use the same criteria range more than once unless we use comparison operators.

This formula, which aims to calculate the total sales amount of product A from both the North and South regions, will not work:

=SUMIFS(E5:E, B5:B, "North", B5:B, "South", C5:C, "A")

So, if you want to consider two criteria in the same column, you can’t specify them directly.

To address this issue, you can nest SUMIFS functions.

However, the following formula, used to sum the total sales between two dates for product A, will work:

=SUMIFS(E5:E, A5:A, ">="&DATE(2023, 9, 22), A5:A, "<="&DATE(2023, 9, 23), C5:C, "A")

Similar: How to Include a Date Range in SUMIFS in Google Sheets.

Nested SUMIFS Functions

Here is an example of nested SUMIFS function usage in Google Sheets. However, I don’t recommend this approach as there is a better method we will cover next.

=SUMIFS(E5:E, B5:B, B1, C5:C, C1)+SUMIFS(E5:E, B5:B, B2, C5:C, C1)

The above SUMIFS formula calculates the total sales of product A in the North and West regions.

The first formula returns the sales in the North, and the second formula returns the sales in the West.

I’m not a big fan of using nested SUMIFS functions in Google Sheets.

The advantage is that it’s straightforward to write, making it accessible for novice users. However, the drawback of this approach is that you may need to nest multiple SUMIFS functions depending on the number of criteria in a column.

REGEXMATCH Workaround

To address the issue of having multiple criteria in the same column, you can approach it as follows:

Match those criteria in the column and return TRUE or FALSE Boolean values. Then, use that result as a virtual criteria range and TRUE as the criterion.

For example, you can use the following REGEXMATCH formula to match “North” and “West” in the region column:

=ArrayFormula(REGEXMATCH(B5:B, "North|West"))

With this, you can write a SUMIFS formula to sum the sales amount in the North and West regions for product A as follows:

=SUMIFS(E5:E, ArrayFormula(REGEXMATCH(B5:B, "North|West")), TRUE, C5:C, "A")

This is a case-sensitive formula that can also match the criteria partially. For more details on this advanced SUMIFS technique, you can read further in the article: REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets.

Wildcards in SUMIFS Function in Google Sheets

The SUMIFS function in Google Sheets accepts wildcards in criteria.

We typically utilize the following two wildcards:

• Asterisk (*): Represents zero or more characters.
• Question mark (?): Represents any single character.

Here’s an example:

=SUMIFS(E5:E16, B5:B16, "N*") // returns 270

This formula calculates the total sales for rows where the “Region” column starts with the letter “N.” It will match “North” in the sample data provided above.

Sometimes, you may have the ? or * character as part of your criteria and do not want the formula to treat it as a wildcard. In that case, you can put a ~ in front of it. The tilde wildcard is used to escape or treat a special character as a literal character.

Related: Three Main Wildcard Characters in Google Sheets Formulas.

Summarizing Data: Examples and Practical Applications

When you want to summarize data in Google Sheets, your primary considerations should be Pivot Tables and the QUERY function.

In addition to those, you can utilize other functions such as a combination of FILTER and aggregation functions, SUMPRODUCT, SUMIF, SUMIFS, and more. Among these options, SUMIFS stands out as a top choice.

We often combine UNIQUE with the SUMIFS function when summarizing data. Let’s explore how to summarize the example table, which offers flexibility for testing different variations of SUMIFS.

Steps:

1. Utilize the following UNIQUE function in cell B19 to retrieve unique records corresponding to the region, product, and customer:

=UNIQUE(B5:D16)

1. We will use the output of this UNIQUE formula as the criteria in SUMIFS.
2. Based on the above example, the outputs are in the range B19:D22. We will use B19:D19 as the criteria in the following SUMIFS formula in E19:

=SUMIFS($E$5:$E$16, $B$5:$B$16, B19, $C$5:$C$16, C19, $D$5:$D$16, D19)

1. It’s important to note that we have used absolute references in the sum range and criteria ranges, while the criteria references are relative.
2. This is because we need to drag this formula down to cell E20 and beyond. This way, the formula will adjust the criteria from each row in the UNIQUE result.

By following these steps, you can effectively summarize your data using the SUMIFS function in Google Sheets.

Summarizing Data Using the SUMIFS Function and MAP Lambda

In the previous SUMIFS function example, we dragged the E19 formula down. If you want the SUMIFS formula to spill down automatically, you can use the MAP Lambda function. Here’s how you can understand and use MAP Lambda with the SUMIFS function in Google Sheets:

Generic Formula:

=MAP(unique_col1, unique_col2, unique_col3, LAMBDA(a, b, c, sumifs_formula)

Where:

• unique_col1 is B19:B22
• unique_col2 is C19:C22
• unique_col3 is D19:D22

In the SUMIFS formula, replace B19 ( criterion1 ) with a, C19 ( criterion2 ) with b, and D19 ( criterion3 ) with c. Here is the formula:

=MAP(B19:B22, C19:C22, D19:D22, LAMBDA(a, b, c, SUMIFS($E$5:$E$16, $B$5:$B$16, a, $C$5:$C$16, b, $D$5:$D$16, c)))

This approach allows the formula to automatically spill down as needed, making it more efficient for summarizing data in Google Sheets.

Related: SUMIFS Array Formula for Expanding Results in Google Sheets.

Troubleshooting SUMIFS Errors and Common Pitfalls

Understanding error types in the SUMIFS functioin and the reasons for them will help you troubleshoot them effectively.

One of the most common errors in SUMIFS is the #VALUE! error, and there are several reasons for it. Here are some of the most prominent ones:

1. Using the criterion first and criteria range second:
   • Example: =SUMIFS(E5:E16, "A", C5:C16)
2. Mixing open and closed ranges:
   • Example: =SUMIFS(E5:E16, D5:D, "Customer1")
3. Forgetting to use the ARRAYFORMULA function when using non-array functions with a criteria range inside the formula:
   • Example: =SUMIFS(E5:E16, MONTH(A5:A16), 9)
4. Using a text column in the sum_range:
   • Example: =SUMIFS(D5:D16, C5:C16, "A", E5:E16)
5. In rare cases, the SUMIFS function in Google Sheets can return a #DIV/0! error. This occurs when the sum_range in filtered rows contains this error.
6. If you see the #NAME? error, it means there might be a typo in function names:
   • Example: =SUMIFSS(E5:E16, MONTH(A5:A16), 9)
7. If you encounter a #REF! error, check for broken cell references. You can find more information on how to resolve such errors in this article: How to Remove #REF! Errors in Google Sheets (Even When IFERROR Fails).

By understanding these common error types and their causes, you can effectively troubleshoot issues in your SUMIFS formulas and improve your data analysis in Google Sheets.

SUMIFS Function Tips and Tricks in Google Sheets

To optimize your data analysis using the Google Sheets SUMIFS function, it’s essential to understand how to handle criteria effectively. Here are some key tips:

Handling Blank and Non-Blank Cells in SUMIFS Criteria:

• To represent an empty string, use "".
• To denote non-blank cells, use <>.

Examples:

• =SUMIFS(C2:C13, A2:A13, "Banana", B2:B13, ""): This formula sums the range C2:C13 if A2:A13 is “Banana” and the cells in the B2:B13 range are blank.
• =SUMIFS(C2:C13, A2:A13, "Banana", B2:B13, "<>"): This formula sums the range C2:C13 if A2:A13 is “Banana” and B2:B13 contains non-blank cells.

Summarizing Based on Data Falling in a Particular Month in a Year:

When working with data spread across multiple years and wanting a summary based on months, avoid relying on month numbers. Instead, use the first date of the month to obtain a month and year-wise summary.

Example:

• To get the total quantity in October 2023, use the criterion DATE(2023,10,1) as follows:

=SUMIFS(C2:C13, ARRAYFORMULA(EOMONTH(D2:D13, -1) + 1), DATE(2023, 10, 1))

The EOMONTH function is used to convert all the dates in October 2023 in the critera range to October 1, 2023.

Conclusion

I’ve aimed to cover everything you need to master the SUMIFS function in Google Sheets.

Here are the key takeaways:

• Using SUMIFS in Google Sheets with multiple criteria.
• Regular expression matching using the REGEXMATCH function.
• Working with wildcards.
• Implementing nested SUMIFS.
• Utilizing the MAP lambda function for array results.
• Troubleshooting common issues.

Additionally, here are a few more helpful tutorials related to this function:

• SUMIF/SUMIFS Excluding Duplicate Values in Google Sheets.
• SUMIFS with OR Condition in Google Sheets.
• Highlight SUMIFS Rows Based on Its Total in Google Sheets.

By exploring these topics, you can become proficient in using the SUMIFS function and handle a wide range of data analysis tasks in Google Sheets.