SUMIF practice in Google Sheets
SUMIF works the same way in Google Sheets as in Excel: SUMIF(range, criterion, sum_range), with the same comparison operators and the same * and ? wildcards. These exercises run in a grid that behaves like Sheets.
How SUMIF works
SUMIF adds up the numbers in the rows that meet one condition. It takes three arguments: the range to test, the condition, and the range to add up.
=SUMIF($B$2:$B$100,"North",$D$2:$D$100) adds the sales in column D for every row where the region in column B is North. Put the condition in a cell instead, as in =SUMIF($B$2:$B$100,F2,$D$2:$D$100), and you can copy the formula down a list of regions to build a summary table.
Conditions can be text, a number, a comparison in quotes such as ">500", or a wildcard such as "*desk*" for any text that contains desk. With two or more conditions, use SUMIFS. The exercises below cover text and number conditions, conditions from a cell, wildcards, and summing one column while testing another.
- 1SUMIF with a text criterionIn D2, use SUMIF to total the Units sold of Apple.
- 2SUMIF with a number conditionPremiumIn D2, use SUMIF to total the amounts greater than 100.
- 3SUMIF with a separate sum_rangePremiumIn E2, use SUMIF to total the Amount of orders with more than 3 units.
- 4SUMIF with the criterion in a cellPremiumIn E2, total the Sales of the rep named in D2. Point SUMIF at D2 instead of typing the name, so the total follows when D2 changes.
- 5SUMIF with a wildcardPremiumIn E1, total the sales of every product with Chair anywhere in its name. Use * on both sides of the word: SUMIF(A2:A8,"*Chair*",B2:B8).
Functions in this topic
Questions
Is SUMIF in Google Sheets the same as in Excel?
Yes. It takes the same three arguments in the same order, and the criterion is written the same way, such as ">100" or "North".
Can SUMIF use a whole column in Google Sheets?
Yes. =SUMIF(A:A,"North",B:B) tests every row in column A and keeps working when you add new rows below. Keep the ranges the same size. The header row in row 1 does no harm, as long as its text does not match the criterion.