SUMIFS In Google Sheets

What Is SUMIFS In Google Sheets?

The SUMIFS in Google Sheets is an inbuilt Math function. It takes a data range and a set of conditions to check in specific ranges as inputs. It then determines the sum of the values in the rows of the cited data range, where all the conditions meet in the corresponding ranges.

Users can use the SUMIFS function in Google Sheets to assess the total financial consultancies in a specific city with revenues exceeding a cited threshold.

For example, the given dataset contains fruits, their categories, stock statuses, and units ordered data.

We must evaluate the total units of fruits ordered, where the fruit category is D, and the stock status is available. We shall take cell H2 as the target cell.

Then, adhering to the definition of SUMIFS in Google Sheets explained above, we shall implement the SUMIFS(), which works like the Excel SUMIFS function, in the required cell to fetch the desired outcome.

In this example, the SUMIFS in Google Sheets arguments are five. The first is the range D2:D13 holding the units ordered values in the specified dataset we aim to add according to the cited conditions. The next argument is the range B2:B13, where we must check the condition specified as the third argument, which is the cell F2 value. Likewise, the fourth argument is the range C2:C13, where we must check the condition specified as the fifth argument, which is the cell G2 value.

So, the function checks for cells in the range B2:B13, where the fruit category is D, which are the cells B10, B12, and B13. Next, it checks for cells in the cells C10, C12, and C13 in the range C2:C13, where the fruit stock status is Available, which are the cells C10 and C12.

Thus, the two conditions are true in rows 9 and 11 of the two ranges, B2:B13 and C2:C13. Next, the function adds the values in rows 9 and 11 of the range D2:D13, which are the cells D10 and D12 values. So, the SUMIFS in Google Sheets returns 147 as the required output.

What Is SUMIFS In Google Sheets?

The function SUMIFS in Google Sheets adds a set of values based on one or more conditions. Furthermore, the function helps perform case-insensitive criteria testing to add values.
The SUMIFS function in Google Sheets is useful for determining values such as the total revenue generated at a specific branch office of a firm till the cited date.
We can use the SUMIFS function with logical operators, wildcards, OR logic, and other inbuilt functions such as ARRAYFORMULA and FIND in Google Sheets to yield practical results.

SUMIFS() Google Sheets Formula

The syntax for the SUMIFS() in Google Sheets is as follows:

Where the SUMIFS in Google Sheets arguments are as follows:

sum_range:The cell range of values we wish to add.
criteria_range1:The cell range to test against the first condition.
criterion1:The first condition, which can be a pattern or test, to apply to the criteria_range1 argument.
criteria_range2, criterion2, …:The additional cell ranges and conditions to test.

Furthermore, ensure to provide the first three arguments when using SUMIFS in Google Sheets, as they are compulsory. However, the remaining arguments are optional.

How To Use SUMIFS Function In Google Sheets?

We can apply the SUMIFS function in Google Sheetsusing the following methods:

Access the function from the ribbon.
Enter the function into the sheet manually.

Method# 1 – Access The Function From The Ribbon 🡪

Select a cell for showcasing the result 🡪 The Insert tab 🡪 The Function optionright arrow 🡪 The Math function group right arrow 🡪 The SUMIFS function.

The required function gets inserted in the target cell. Next, update the SUMIFS function argumentswithin the brackets.

Furthermore, clicking the ‘?’ icon adjacent to the function name will show the syntax of the function SUMIFS in Google Sheets explained in the previous section.

Next, we can select the down arrow in the window to view the function’s detailed explanation.

Finally, after entering the arguments separated by commas, press Enter to view the value the function SUMIFS in Google Sheets returns as the required outcome.

Method #2 – Enter The Function Into The Sheet Manually 🡪

Choose the cell where we wish to show the output.
Type =SUMIFS( in the cell. [ Alternatively, type =S or =SU and click the function SUMIFS from the list of suggestions to choose the function.]

Type in the argument values, with commas in between, and close the brackets.
Press Enter to fetch the SUMIFS() output.

Examples

We shall see the effective ways of using SUMIFS in Google Sheets with illustrations.

Example #1 – SUMIFS With Empty And Non-empty Cells

The first dataset lists employees, their tasks completed count and the task completion dates.

The task is to calculate the total tasks completed by the employee Anna Thomas, cited in cell F1. Also, the task completed date should be non-blank to consider the tasks completed values for addition. Assume cell F3 is the target cell.

Step 1: Select cell F3 and enter the SUMIFS().

=SUMIFS(

[ Alternatively, choose the required cell. Next, select Insert 🡪 Function 🡪 Math 🡪 SUMIFS function.

The above action will show the function in the target cell.]

Step 2: Feed the function arguments within the brackets.

=SUMIFS(B2:B13,A2:A13,F1,C2:C13,”<>”)

Step 3: Press Enter to secure the SUMIFS function output.

The SUMIFS() accepts five arguments. The first is the range B2:B13 holding the tasks completed values we aim to add based on multiple criteria.

The second is the range A2:A13, where we must check the first condition, cited by the third argument, which is the cell F1 value. Next, the fourth is the range C2:C13, where we must check the second condition, cited by the fifth argument, which is the cell F2 value.

So, the function first checks the cells in the range A2:A13, where the employee’s name is Anna Thomas, which are the cells A3, A6, A8, and A11. Next, it looks for cells among the cells C3, C6, C8, and C11 in the range C2:C13, where the task completion date is a non-blank value, which are cells C3, C6, and C11.

Thus, the two conditions are true in rows 2, 5 and 10 of the two ranges, A2:A13 and C2:C13. Next, the function sums the values in rows 2, 5 and 10 of the range B2:B13, which are the cells B3, B6, and B11 values, to return 74 as the desired output.

Please note that we use the Not Equal To logical operator (‘<>’) in the second condition to add non-empty cells while counting 0-length strings. On the flip side, we can use the Equal To operation (‘=‘) to add empty cells and ‘“”’ to add blank cells, counting 0 length strings, according to our requirements.

Example #2 – SUMIFS With OR Logic

The following dataset contains a set of products, their brands and date-wise sales data.

The requirement is to compute the total sales value, with the product being a Laptop or Desktop and the date value being 2 July 2024, as cited in G1, I1, and the merged cell G2. We will consider the merged cell G3 as the target cell.

Step 1: Select cell G3, enter the required SUMIFS(), and press Enter.

=SUMIFS(D2:D13,A2:A13,G1,C2:C13,G2)+SUMIFS(D2:D13,A2:A13,I1,C2:C13,G2)

The formula contains two SUMIFS functions. Each SUMIFS() takes five arguments.

While all the argument values are the same in both functions, only the third argument value differs.

The first is the range D2:D13, containing the sales figures we aim to add based on multiple conditions.

The second argument is the range A2:A13, where we must check the first condition. The first condition is specified by the third argument. It is the cell G1 value and I1 value in the first and second SUMIFS functions, respectively. Next, the fourth is the range C2:C13. Here, we must check the second condition, cited by the fifth argument, which is the cell G2 value.

So, the first SUMIFS() checks the cells in the range A2:A13. Here, the product is the Laptop, which are the cells A2, A4, A7, and A11. Next, it looks for cells among the cells C2, C4, C7, and C11 in the range C2:C13. Here, it checks where the date is 2 July 2024, which are the cells C2 and C7.

Thus, the two conditions are true two rows 1 and 6 of the two ranges, A2:A13 and C2:C13. Next, the function sums the values in rows 1 and 6 of the range D2:D13. These are the cells D2 and D7 values, to return $4,100 as the desired output.

Later, the second SUMIFS() checks the cells in the range A2:A13, where the product is Desktop, which are the cells A3, A8, A9, and A13. Finally, it looks for cells among the cells C3, C8, C9, and C13 in the range C2:C13. Here, the date is 2 July 2024, which are the cells C8 and C13.

Thus, the two conditions are true two rows 7 and 12 of the two ranges, A2:A13 and C2:C13. Next, the function adds the values in rows 7 and 12 of the range D2:D13, which are the cells D8 and D13 values, to return $3,200 as the desired output.

Finally, the formula adds the two SUMIFS function outputs to return $7,300 as the required total sales value.

Example #3 – SUMIFS With Logical Operators

The given dataset lists stationery items, the store details, and the order date-wise inventory level data.

We must determine the total inventory level of stationery items, provided the individual inventory levels are above 100 and ordered by 12 July 2024. We shall showcase the result in cell G3.

Step 1: Select cell G3, enter the SUMIFS(), and press Enter.

=SUMIFS(C2:C13,C2:C13,”>”&G1,D2:D13,”<“&G2)

SUMIFS With Logical Operators Example 3-1

The SUMIFS() takes five arguments as input.

The first is the range C2:C13, containing the inventory levels we wish to add based on the specified criteria.

The second argument is the range C2:C13, where we must find cells holding inventory levels over 100, as indicated by the third argument. Next, the fourth argument is the range D2:D13, where we must find cells containing dates before 12 July 2024, as denoted by the fifth argument.

So, the first condition holds in cells C4:C11 and C13. Next, the second condition holds in cells D4 and D8:D11.

Thus, the two conditions hold in rows 3 and 7:10 of the ranges C2:C13 and D2:D13. So, the function sums the values in rows 3 and 7:10 of the range C2:C13, which are the cells C4 and C8:C11 values, to return 800 as the desired result.

Please note that we use the appropriate logical operators in the third and fifth arguments that furnish the required conditions. In this case, we use the ‘>’ and ‘<’ logical operators, similar to Excel logical operators. However, the other logical operators we can use according to our requirements are ‘=’, ‘<>’, ‘>=’, and ‘<=’.

Example #4 – SUMIFS With Wildcards

The dataset below contains the units sold data of item codes at various branch offices of a firm.

The aim is to compute the total units sold data for item codes, starting with NTC and at the Atlanta branch office. Consider cell F3 as the target cell.

Step 1: Select cell F3, enter the SUMIFS(), and press Enter.

=SUMIFS(C2:C13,B2:B13,F2,A2:A13,”NTC*”)

The SUMIFS() takes five arguments as the input.

The first is the range C2:C13 holding the units sold values we aim to add based on the cited criteria.

The second argument is the range B2:B13, where we must find cells containing the branch office, Atlanta, as indicated by the third argument. Next, the fourth argument is the range A2:A13, where we must find cells containing the item code starting with NTC, as denoted by the fifth argument.

So, the first condition holds in cells B2, B5, B9, and B13. Next, the second condition holds in cells A2 and A13.

Thus, the two conditions hold in rows 1 and 12 of the ranges A2:A13 and B2:B13. So, the function sums the values in rows 1 and 12 of the range C2:C13, which are the cells C2 and C13 values, to return 101 as the desired result.

Please note that we use the wildcard character ‘*’, similar to wildcard in Excel, after the text “NTC” in the fifth argument, denoting the second condition. The reason is that the required item code starts with the text “NTC” followed by multiple characters. So, the ‘*’ wildcard character represents the set of characters after the cited text. It helps the SUMIFS() to perform a partial match between the condition and the corresponding cell range. On the flip side, if there was only one character after the text “NTC”, we could use the wildcard character ‘?’, which represents a single character. Also, we can place these wildcards before, in between, or after the specified text, according to our requirements.

Important Things To Note

Ensure the directly supplied criterion1, criterion2,…argument values to the SUMIFS in Google Sheets are in double quotations. Otherwise, the function may return the value of 0.
Ensure the ranges, supplied as the sum_range, criteria_range1, criteria_range2,… argument values to the SUMIFS(),include the same number of rows and columns. Otherwise, the function returns the #VALUE! error value, similar to #VALUE! error in Excel.

Frequently Asked Questions (FAQs)

How to use Google Sheets SUMIFS with Checkboxes?

We can use Google Sheets SUMIFS with Checkboxes as explained below with an illustration.
The following dataset contains a list of sales representatives, their teams, and sales-generated data.

We must fill the audit completion status in column D using checkboxes. Next, we must evaluate the total sales generated, provided the team is HTC and the audit status is complete. We shall consider cell G3 as the target cell.

Step 1: Select the range D2:D11 and choose Insert 🡪 Checkbox.

We shall see the checkboxes in the chosen cells.

Step 2: Click inside the checkboxes in the required column D rows to indicate the audit is complete for the specific sales representative in the cited team.

Step 3: Select cell G3, enter the SUMIFS(), and press Enter.

=SUMIFS(C2:C11,B2:B11,G1,D2:D11,”TRUE”)

The first argument in the SUMIFS() is the range C2:C11 containing the sales figures we need to add based on the specified conditions.

The second is the range B2:B11, where we must check the first condition, cited by the third argument, which is the cell G1 value. After that, the fourth is the range D2:D11, where we must check the second condition, cited by the fifth argument, which is the Boolean value TRUE.

So, the function first checks the cells in the range B2:B11, where the team name is HTC, which are the cells B2, B3, B5, and B9. Next, it looks for cells among the cells D2, D3, D5, and D9 in the range D2:D11, where the audit completion status is true (indicated by a checked box), which are the cells D2, D5, and D9.

Thus, the two conditions are true two rows 1, 4, and 8 of the two ranges, B2:B11 and D2:D11. Next, the function sums the values in rows 1, 4 and 8 of the range C2:C11, which are the cells C2, C5, and C9 values, to return $12,800 as the required output.

Is SUMIFS in Google Sheets case-insensitive?

SUMIFS in Google Sheets case-insensitive.

How many criteria can SUMIFS have in Google Sheets?

The SUMIFS can have a maximum of 127 criteria in Google Sheets.

Download Template

This article must be helpful to understand the SUMIFS In Google Sheets, with its formula and examples. You can download the template here to use it instantly.

SUMIFS In Google Sheets Download

SUMIFS In Google Sheets