Mastering data analysis with the SUMIF function in Google Sheets! This essential tool allows you to add up values that meet specific criteria, making it perfect for tracking expenses, analyzing sales data, and more. With SUMIF in Google Sheets, you can effortlessly sum values based on conditions you set, streamlining your data management tasks.
Syntax and Basic Uses of SUMIF in Google Sheets
Understand the basic syntax and uses of the SUMIF function in Google Sheets to get started with this powerful tool.
=SUMIF(range, criterion, sum_range)
range: Range of cells where the condition has to be checked.
criterion: The condition that has to be matched with the cells specified in the range above.
sum_range(optional): The range of cells with numerical data that has to be summed up. If this is not specified, then the first range is checked for condition and summed up.
SUMIF is useful in managing financial records or databases, tracking sales performance, and much more.
SUMIF Formula Examples in Google Sheets
Explore various examples of SUMIF formulas to understand how to use this function in different scenarios.
1. SUMIF Formulas with Text Criteria
Let us consider the following data. Table 1 "Book-wise Data" has a list of books along with their genre and no. of available copies.
(i) SUMIF equal to (exact match):
=SUMIF(C3:C18,"Adventure",D3:D18)
Explanation: Finds "Adventure" in the range C3:C18 and sums their corresponding values found in range D3:D18. Hence it will give us the total number of books of the adventure genre.
Note: The condition should always be written within double quotes("") and it is not case sensitive-"Adventure" and "adventure" are considered the same.
(ii) SUMIF not equal to (Exact match)
=SUMIF(C3:C18,"<>Adventure",D3:D18)
Explanation: The formula returns the sum of all those books whose genre is NOT "adventure".
2. SUMIF Formulas with WildCard Characters
The following formulas are useful to match the text partially with the cell contents.
(i) SUMIF cell contains (Partial Match)
=SUMIF(B3:B18, "*abridged*", D3:D18)
Explanation: This gives us the number of books which are abridged. It looks for the word "abridged" in the cell range B3:B18 and sums corresponding values found in range D3:D18.
(ii) SUMIF cell does not contain (Partial match)
=SUMIF(B3:B18, "<>*abridged*", D3:D18)
Explanation: Whichever cell in range B3:B18 does not contain the text "abridged", its corresponding number of copies is summed up to give the result.
(iii) Case-sensitive SUMIF in Google Sheets
As already mentioned above, the SUMIF function is case-insensitive, to differentiate between uppercase and lowercase text, the following syntax can be used.
=SUMIF(ArrayFormula(EXACT(criterion, range)), TRUE, sum_range)
or
=SUMIF(ArrayFormula(FIND(criterion, range)), 1, sum_range)
In our case, if we want to find books that belong to genre "Adventure" only and not "adventure", the formula would look like:
3. How SUMIF-EXACT/FIND formula works
=SUMIF(ArrayFormula(EXACT(criterion, range)), TRUE, sum_range)
ArrayFormula makes EXACT comparisons to the criterion provided with the values in range. If an Exact Match is found, the formula returns True, otherwise False.
Out of the True and False values in the range, SUMIF searches for True values and sums up corresponding values in Sum_range.
Similarly, if we use the FIND function separately, it will stop searching as soon as it finds the first match. Wherever the match is found, it returns a 1 and #VALUE! error for cells where the value is not found.
=SUMIF(ArrayFormula(FIND(criterion, range)), 1, sum_range)
4. SUMIF formulas for numbers
The criterion need not always be text. Let's say we want to find the number of books whose number of copies is more than 50. In such cases the following formulas can be used.
(i) SUMIF greater than or less than
To find the number of books whose number of copies is more than 50,
=SUMIF(C22:C26, ">50")
Here the sum_range has been eliminated since the range and sum_range both were the same in this case. In cases where they are different, sum_range needs to be specified.
=SUMIF(D3:D18, "<50",D3:D18)
This can also be done using cell referencing using the "&"(ampersand) . If the value in cell D21 is 50 then we can use:
=SUMIF(C22:C26, ">"&D21)
For other comparison criteria, the following logical operators can be used.
Greater than | > |
|---|---|
Greater than or equal to | >= |
Less than | < |
Less than or equal to | <= |
(ii) SUMIF equal to
To find the number of books whose number of copies is equal to 50,\
=SUMIF(C22:C26, 50)
or
=SUMIF(C22:C26, "=50")
or (using cell referencing, if cell D21 has value 50)
=SUMIF(C22:C26, D21)
Note: If the value is directly being specified, then it should be written without quotation marks, otherwise if it is being used with "=" sign, then quotation marks should be used.
Again, if the sum_range is not the same as range then it needs to be specified separately.
(iii) Sum if not equal to
This is similar to the not equal to criterion in text. To find the total number of books whose number of copies is not equal to 50, we can use the formula:
=SUMIF(C22:C26, "<>50",C22:C26)
or (using cell referencing, if cell E2 has value 50)
=SUMIF(C22:C26, "<>"&D21,C22:C26)
5. Google Sheets SUMIF formulas for dates
Google sheets can sum up values based on dates but a specific format for comparing the dates is to be followed. If I want to sum values of books bought before a date of format DD/MM/YYYY, then we can use one of the following:
=SUMIF(range, "<MM/DD/YYYY", sum_range)
or
=SUMIF(range, "<"&DATE(YYYY,MM,DD), sum_range)
or if the date is written in the cell E22 in the format MM/DD/YYYY, then
=SUMIF(range, "<"&E22, sum_range)
Similarly, other logical operators given in the table earlier in this article can be used for other comparisons.
To sum cells based on TODAY's date, TODAY() function can be included.
For example, to check number of books bought today (today is 30 May 2024),
=SUMIF(C22:C26, TODAY(), D22:B26)
6. Sum based on blank or non-blank cells
To sum values whose value in a certain column is blank or not blank we may use the following in criterion:
"=" (SUMIF Blank) | completely blank cells |
|---|---|
""(SUMIF Blank) | cells including those that contain zero length strings |
"<>"(SUMIF Not blank) | cells with any value, including zero length strings |
(i) SUMIF Blank
To find the total number of books whose date of purchase is not mentioned:
=SUMIF(C22:C26,"", D22:D26)
(ii) SUMIF Not blank
To find the total number of books whose date of purchase is mentioned(not blank):
=SUMIF(C22:C26,"<>", D22:D26)
7. Google Sheets SUMIF with multiple criteria (OR logic)
Google sheets lets us sum values based on multiple criteria. Let's say we want to find the total number of books belonging to genre "Horror" or "Thriller", we can apply the multiple criteria formula:
=SUMIF(C3:C18,"Horror",D3:D18)+SUMIF(C3:C18,"Thriller",D3:D18)
This formula works even if any one of the criteria is fulfilled (OR Logic).
Google Sheets SUMIF - Things to Remember
Now that we know a lot about the usage of SUMIF in google sheets, let us not forget to keep these points in mind while using this function:
1. SUMIF can evaluate only one condition
The SUMIF syntax allows only using one range, one criterion and one sum_range. To apply multiple criteria, two formulas can be added (as shown above)
2. The SUMIF function is case-insensitive
If we put "Adventure" in the exact match criterion, then it considers both "adventure" and "Adventure" as same and sums up corresponding values for both.
3. Supply equally sized range and sum_range
Only equal sized ranges can be used. If the range is C3:C18 then sum_range must be D3:D18 and not D3:D20.
4. Mind the syntax of SUMIF criteria
If the criterion is text, wildcard character or logical operator (followed by a number, text or date) it must be enclosed in quotation marks.
=SUMIF(C3:C18,"Horror",D3:D18) =SUMIF(C3:C18,"*",D3:D18) =SUMIF(D3:D18,">50") =SUMIF(C3:C18,"<>Horror",D3:D18)
Otherwise, if it contains a logical operator and a cell reference or another function then the cell reference or function should not be enclosed in quotes.
=SUMIF(D3:D18,">"&E2) =SUMIF(D3:D18,">"&TODAY())
5. Lock ranges with absolute cell references if needed
In cases where we autofill the cells with SUMIF formula or copy-paste the formula somewhere else and we do not want the cell addresses to change then we must lock them using $ symbol.
=SUMIF($C$3:$C$18,"<>Horror",$D$3:$D$18)