There are many reasons why you may need to sum the values of cells in Google Sheets. The SUM function and the ‘Autosum’ button are helpful but not very flexible. If you want to sum cells that meet specific criteria, you should consider using Google Sheets’ builtin functions: SUMIF and SUMIFS. The first allows you to specify a range of cells and the criterion cells must meet to be summed, while SUMIFS allows you to specify multiple ranges and criteria. Show
In this post, you will learn about the SUMIF and SUMIFS functions in Google Sheets. After reviewing the syntax for both functions, you will learn how to use them in different circumstances. You have a variety of examples with stepbystep instructions using different types of criteria, such as adding values based on the text contained in cells and values that fall within a specific date range. Additionally, you will learn how to use the SUMIFS function to sum values based on multiple criteria. SUMIF & SUMIFS Function Syntax in Google SheetsLet’s review the syntax for SUMIF and SUMIFS before using them. SUMIF Function SyntaxGoogle Sheets’ SUMIF function has two required parameters and an optional parameter. =SUMIF(range, criterion,[sum_range])
SUMIFS Function SyntaxGoogle Sheets’ SUMIFS function has three required parameters. However, SUMIFS allows you to provide additional parameters to test multiple ranges using different criteria. =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]) The sum_range, the first range, and the first criterion are required arguments.
Additional ranges and criteria are optional, but you can add as many as you want. However, the dimensions of subsequent ranges must be equal to those of the first range  all ranges should have the same number of rows and columns.
How to Build the Criteria or Conditions?There are many useful operators you can use to define the criteria or conditions in the SUMIF and SUMIFS functions. For example, you can use the following criteria to search for nonblank and blank cells, respectively. =COUNTIF(range, “<>”) Numeric ValuesWhen dealing with exact numeric values, you don’t need to use quotes. However, if you're using comparison operators, you’ll need the quotes to enclose the condition or criterion.
Text ValuesYou also have a lot of options in terms of text values. In addition to matching exact text by using quotation marks, you can also use any of the following wildcards for partial matches.
Examples of How to Use the SUMIF Function in Google SheetsNow that you have reviewed the function’s syntax, it's time to see some examples of how to use SUMIF in different situations. Imagine you have a table with sales data, which includes the date, the name of the sales agent, their team, the product, and the sales amount. You need to get different counts for your sales report, but counting manually would be a nightmare, as the table has five hundred rows. Google Sheets Sum If Cell Contains TextFor this example, I want to sum the sales amounts corresponding to ‘productA’.
Imagine that you need to sum values based on the date when sales date rather than the product or any other text value. For this example, I will find the total amount for sales made on or after February 1st, 2022.
Examples of How to Use the SUMIFS Function with Multiple Criteria in Google SheetsThe COUNTIFS function allows you to sum values based on multiple criteria, which can be applied to multiple ranges. For the sake of simplicity, the examples below are based on the same dataset as the one used for SUMIF. SUMIFS with Multiple Criteria Applied to Multiple RangesFor this example, I want to sum the amounts for all sales of productA made on or after February 1, 2022.
SUMIFS with Multiple Criteria Applied to One RangeSUMIFS can also be used by applying multiple criteria to a single range. Let’s see how you can find the total sales amount for sales with a value between $500 and $1000.
How to Automate Your Data on Top of Google Sheets?Layer is an addon that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works. Using Layer, you can:
Limited Time Offer: Install the Layer Google Sheets AddOn today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets! ConclusionSUMIF and SUMIFS are versatile and easytouse functions. You can add values based on single or multiple criteria, and these criteria can be applied to single or multiple ranges. You now know the syntax of both functions and how to build criteria using comparison and logical operators on numeric and text values. You also have examples of various scenarios using both functions, with stepbystep instructions. To learn more about the related functions COUNTIF and COUNTIFS, check out our post on COUNTIF & COUNTIFS in Google Sheets: Formula & Examples. How do I use Sumif with multiple criteria in Google Sheets?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: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
Can I use Sumif for multiple criteria?The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria.
Can you do Sumifs with 3 criteria?SUMIFS function with multiple criteria based on OR logic
As SUMIFS function by default entertains multiple criteria based on AND logic, but to sum numbers based on multiple criteria using OR logic, you need to SUMIFS function within an array constant.
How many criteria can Sumifs have in Google Sheets?Unlike the SUMIF function, which adds values to a sum based on a single condition, SUMIFS can enforce two or more conditions.
