SUMIF is one of the most popular functions in Google Sheets and Excel, widely utilized among spreadsheet users. While it is easy to learn, it proves to be a valuable tool for advanced data manipulation in your Google Sheets toolbox.
This tutorial serves as a starting point for understanding and using the SUMIF function in Google Sheets. Once mastered, you can explore this blog for numerous advanced-level tutorials on SUMIF and related functions. Relevant links to these tutorials will be provided within this tutorial.
The primary purpose of SUMIF is conditional summation. It calculates the sum of a specified range based on a condition within the same or another range.
SUMIF Function in Google Sheets: Syntax and Arguments
Here are the syntax and a brief overview of the arguments in use. We will delve into the formula examples in the next section.
Syntax:
SUMIF(range, criterion, [sum_range])Arguments
range: The range tested against the criterion. It can be a physical range like A1:A500 or an expression, such as the result of another formula (e.g., IMPORTRANGE).criterion: The pattern or test applied to the range. It should match the type of content in the range, such as a number, date, time, etc. If multiple criteria are used, wrap the formula with the ARRAYFORMULA function.sum_range: The range to be summed. Specify it only if it differs from therange.sum_rangemust be a physical range, which is a limitation of the SUMIF function.
Basic Examples of the SUMIF Function in Google Sheets
Let’s create a small table in Google Sheets and explore some basic SUMIF formula examples. This will help you understand the purpose and usage of the function.
Sample Data:
Firstly, create a sample dataset as shown below in A1:C6.
| Product Name | Date | Qty. Sold |
| TV | 01/10/2017 | 10 |
| TV | 15/11/2017 | 3 |
| Projector | 15/11/2017 | 2 |
| Projector | 15/12/2017 | 3 |
| TV | 15/12/2017 | 7 |
Example Formulas
To find out how many TV units were sold:
Formula:
=SUMIF(A1:A6, "TV", C1:C6)To find the number of projectors sold:
Formula:
=SUMIF(A1:A6, "Projector", C1:C6)Key Points:
- You can enter the criterion in any cell (e.g., C2) and replace the criterion in the formula with the cell reference:
=SUMIF(A1:A6, C2, B1:B6) - The SUMIF function in Google Sheets is not case-sensitive.
- When hardcoding the criterion (entering it directly into the formula), use double quotes for text criteria, enter numbers without double quotes, represent dates using the DATE function with the syntax
DATE(year, month, day), express time using the TIME function with the syntaxTIME(hour, minute, second), and combine date and time as a timestamp with the syntaxDATE(year, month, day)+TIME(hour, minute, second). - It is advisable to input the criterion within a cell and refer to that cell in the formula, ensuring that you don’t need to worry about remembering the specific syntax.
You May Like: How to Utilise Google Sheets Date Functions [Complete Guide]
SUMIF by MONTH (The Proper Way):
To total quantities sold on 15 November 2017:
=SUMIF(B1:B6, DATE(2017, 11, 15), C1:C6)How do we find the total quantity sold in a specific month, for example, December?
Since our range only contains dates, we need to convert it to months before using the criterion, i.e., 12.
To achieve this, we can wrap the date range with the formula ARRAYFORMULA(MONTH(DATEVALUE(range))).
=SUMIF(ARRAYFORMULA(MONTH(DATEVALUE(B2:B6))), 12, C2:C6)SUMIF by MONTH and YEAR (The Proper Way):
To utilize SUMIF by month and year, you should transform the dates in the range to the beginning of the month using the syntax ARRAYFORMULA(EOMONTH(DATEVALUE(range), -1)+1). It’s important to note that the criterion must be in the format DATE(year, month, 1).
For instance, to calculate the total quantities sold in December 2017 (month and year), you can employ the following SUMIF formula in Google Sheets:
=SUMIF(ARRAYFORMULA(EOMONTH(DATEVALUE(B2:B6), -1)+1), DATE(2017, 12, 1), C2:C6)SUMIF Array Formula (Multiple Criteria and Multiple Results):
To retrieve the total values of “TV” and “Projector” simultaneously in Google Sheets, you can employ the ARRAYFORMULA function in conjunction with the SUMIF function.
It’s essential to specify the criteria precisely. For a horizontal result, utilize the HSTACK function with the syntax HSTACK(criterion1, criterion2). For a vertical result, substitute HSTACK with VSTACK.
Example:
=ArrayFormula(SUMIF(A2:A6, VSTACK("TV", "Projector"), C2:C6))Here is how to use cell references:
=ArrayFormula(SUMIF(A2:A6, E2:E3, C2:C6))
ISBETWEEN and Comparison Operators in SUMIF in Google Sheets:
At times, you may need to sum a range that falls within a specific range. For instance, to sum the quantities sold on or after December 1, 2017, use the following formula:
=SUMIF(B2:B6, ">="&DATE(2017, 12, 1), C2:C6)You can similarly use other comparison operators.
To sum the quantities sold within a specific range, such as November 1, 2017, to December 31, 2017, employ ISBETWEEN with the syntax ISBETWEEN(range, lower_value, upper_value), and the criterion must be TRUE.
=SUMIF(ISBETWEEN(B2:B6, DATE(2017, 11, 1), DATE(2017, 12, 31)), TRUE, C2:C6)ISBETWEEN evaluates the range and returns TRUE or FALSE, so the criterion must be set to TRUE.
Two Miscellaneous Examples:
Here are two additional examples that illustrate how wildcards can be used with criteria for a partial match of text criteria. The second example demonstrates a scenario where the range and sum_range are the same.
=SUMIF(A2:A6, "pr*", C2:C6) // sums the quantity for the range starting with the string "pr"
=SUMIF(C2:C6, ">"&2) // returns the total of quantities greater than 2Example of Real-life Use of the SUMIF Function in Google Sheets
Here is a real-life example of using the SUMIF function in Google Sheets. If you wish, you can copy the sample sheet with formulas by clicking the button below.
In a cable laying contract, let’s assume our client issued several HT cables, and we have recorded the transactions in Google Sheets in “Sheet1” within the range A3:C.
In the same sheet, column D (D3:D) contains the cables that were issued to the site (employee) for laying, and the remaining balance is recorded in column E (E3:E).
Upon checking column B, you may notice the receipt of the same cables multiple times. Let’s summarize this information in “Sheet2” using the SUMIF function in Google Sheets.
In cell B4 on Sheet2, use the UNIQUE formula below to obtain the unique cable names:
=UNIQUE(Sheet1!B4:B)This will serve as the criteria in a SUMIF array formula in cell C4.
Now, in Sheet2, in cell C4, use the formula below to calculate the total received quantity:
=ArrayFormula(SUMIF(Sheet1!B4:B, B4:B, Sheet1!D4:D))Note: This SUMIF formula may leave trailing zeros wherever Sheet2!B4:B is blank (no criterion). You can remove them in many ways, and here is my suggested formula:
=ArrayFormula(LET(total, SUMIF(Sheet1!B4:B, B4:B, Sheet1!D4:D), IF(total=0, ,total)))We utilized the LET function to assign the name ‘total’ to the SUMIF formula and employed an IF logical test to return a blank value if the total is zero.
This approach allows us to create summary reports using the SUMIF function in Google Sheets.
Advanced-Level Tutorials for the SUMIF Function in Google Sheets
To simplify your tasks, I am providing explanations on solving common challenges in Google Sheets using the SUMIF function. Explore this exceptional collection of unique SUMIF formula examples with full access.
Handling Hidden Rows
If you utilize the Filter menu or manually hide rows in Google Sheets, exercise caution when employing the SUMIF function. The function will encompass hidden or filtered-out rows.
The sole method to exclude filtered-out or hidden rows is by employing SUBTOTAL in a helper column. Subsequently, use that column in SUMIFS (not SUMIF) as one of the criterion fields.
I understand that some of you may not prefer using helper columns. In such cases, you can employ a virtual helper column.
Month and Year in Range Column
Here’s another common task, which we’ve already covered in our basic examples above. Here, I am attempting to simplify the use of SUMIF in a date column in Google Sheets.
In the sample data below, the dates in column A are distributed across different years.
Using the month as the criterion alone may yield an incorrect result.
How do you use both month and year as the condition?
Using Multiple Sum Ranges in SUMIF Function in Google Sheets
The supported arguments for the SUMIF function include a range, criterion, and sum_range.
This limitation of the function might become apparent in various cases.
For instance, consider having an item-wise sales report for two salespersons displayed side by side.
Given that the sales volume is in two columns, how do you sum these two columns based on conditions?
- Sum Range: Columns B and C (two columns to sum)
- Criterion Range: Column A
I have experimented with various functions, including SUMIF, in this scenario.
Using Named Ranges in Conditional Sum
I’m not sure why I’m not using named ranges more frequently; it’s not a practice I tend to favor. However, many Google Sheets/Excel users heavily rely on named ranges to streamline their formulas.
Having actively participated in Google Docs forums as a recognized Product Expert in Google Sheets, I’m well aware of the preferences and practices of Google Sheets users.
I acknowledge that incorporating named ranges in Google Sheets can add a touch of sophistication to my SUMIF formulas.
Switching Sum Column (SUMIF + Named Ranges + Data Validation):
Multiple Conditions
The SUMIF conditional sum function can handle multiple conditions in a single formula with the help of the ARRAYFORMULA.
For the criterion, select the cells that contain the conditions, enter them as comma-separated values within curly brackets, or use VSTACK or HSTACK functions. We have already explored a basic example above.
For real-life examples, read more.
Expanding Dynamic Range
You can use infinite (open) ranges in formulas like A1:A or A:A to encompass an entire column. This allows your formula to automatically include newly added rows.
However, if you have two data ranges, one below the other, the above infinite (open) range in the formula may not work as intended.
To make a formula dynamic to some extent in Google Sheets, consider the information in the image below.
We only want to specify the range of the first table, but the formula should automatically expand or shrink based on this table.
Simplify the Use of the SUMIF Function by Mastering Criterion Usage
You can quickly learn any functions that involve conditions if you understand how to apply various types of conditions such as date, text, and number.
Although the SUMIF function in Google Sheets is categorized under “Maths,” it operates based on conditions.
Therefore, it’s crucial to master the usage of conditions or criteria within it. I already have a well-explained tutorial on that.
Common Errors and How to Handle Them in Google Sheets SUMIF Function
Here are some of the most common mistakes we may unintentionally make when using the SUMIF function.
Mismatched Data Types:
Ensure the data types in the criteria range match the type expected by the criterion. For instance, if the criterion is a text string, ensure the values in the criteria range are also text; otherwise, the formula may return 0.
Blank Cells in Date Range:
When using a month criterion in a date range (e.g., 12), any value in the sum_range corresponding to a blank cell in the range will be added. Use DATEVALUE, as explained in the basic example section above, to avoid this.
Remove Extra Spaces:
If dealing with data from external sources or entered by a newcomer, remove any additional, double, or extra spaces by selecting the range and applying Data > Data clean-up > Trim whitespace.
Common Errors:
#ERROR!– Formula Parse Error. Make sure that, you have delimited the arguments.#N/A– Argument must be a range: Make sure that thesum_rangeis a physical range, not an expression.#REF!– You may be using multiple criteria and the formula has no blank cells to expand or spill the result.
Conclusion
Several functions can be employed for conditional sums, including SUM+FILTER, QUERY, and SUMPRODUCT. However, among these, SUMIF stands out as the simplest option. If you need to test criteria in more than one column (range), you can turn to SUMIFS.
In SUMIF, we utilize the range and sum_range. When transitioning to its sibling, SUMIFS, the range becomes criteria_range1, criteria_range2, …, facilitating the incorporation of multiple criteria.
Feel free to use the search feature within this blog to discover various tutorials on SUMIF and SUMIFS.
