SUMIF practice exercises

Add up only the rows that meet a condition, with the condition in the same column or in another one.

Start the first exercise

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.

  1. 1SUMIF with a text criterionIn D2, use SUMIF to total the Units sold of Apple.
  2. 2SUMIF with a number conditionPremiumIn D2, use SUMIF to total the amounts greater than 100.
  3. 3SUMIF with a separate sum_rangePremiumIn E2, use SUMIF to total the Amount of orders with more than 3 units.
  4. 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.
  5. 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

Read the guide

Questions

How do I use SUMIF with multiple criteria?

Use SUMIFS instead. It takes the sum range first, then a range and a criterion for each condition, as in =SUMIFS(D2:D10,B2:B10,"North",C2:C10,">100"). Every condition must be true for a row to count. If you want rows that match one condition or the other, add two SUMIF formulas together.

How do I use SUMIF with dates?

Put the comparison in quotes and join the date with &, as in =SUMIF(A2:A10,">="&F1,B2:B10), which adds the amounts dated on or after the date in F1. SUMIF can test only one limit. To sum between two dates, use SUMIFS with the date column twice.

Why does SUMIF return 0?

Usually no row matches the criterion. Check for extra spaces and typos in the criterion. If the numbers in the sum range are stored as text, SUMIF skips them and you also get 0. Numbers that sit at the left edge of a cell are often text.