SUMIF and SUMIFS practice for useful reports

Real reports often need a subtotal rather than the sum of every row. SUMIF totals rows that match one criterion. SUMIFS handles several criteria at once. Their argument order differs, so reading the formula aloud can help avoid a common mistake.

One condition with SUMIF

In the Sumwarden sales dataset, region is in B2:B9 and revenue is in E2:E9. To total revenue for North, check the region cells, match the text “North,” and add the corresponding revenue cells:

=SUMIF(B2:B9,"North",E2:E9)

The ranges align by position. The first region cell describes the first revenue cell, the second describes the second, and so on. Keep ranges the same size and on matching rows.

Several conditions with SUMIFS

To report only North-region Keyboard sales, add a product criterion. SUMIFS begins with the range to sum, followed by range/criterion pairs:

=SUMIFS(E2:E9,B2:B9,"North",C2:C9,"Keyboard")

Every criterion must be true for the same row. This is an AND relationship. If you need North or South, two separate SUMIF calculations added together may be easier to understand for a small report.

Numeric criteria

Criteria can include comparison operators such as “>700” or “>=10.” Those operator-and-value criteria go in double quotes. You can also build a criterion from an operator and a cell reference in Excel, using concatenation.

Audit the subtotal

Filter the source data mentally or in Excel and verify which rows should contribute. A correct-looking number is weak evidence if the ranges point to different sections of the sheet. As your report grows, use consistent ranges and keep source data organized with one header row.

Move beyond the example

Use the same pattern for costs by department, hours by team, or units by SKU. For a segment’s profit, calculate matching revenue and matching cost using the same filters, then subtract. The Crystal Mines teach the individual tools; the Northern Contract boss combines them into a segment report.

Continue your adventure

Use the formula spellbook for syntax or explore the full quest map. You can practice in the browser and download workbooks without creating an account.