SUMIFS practice exercises

SUMIFS adds up the numbers in one column, but only for the rows that meet every condition you give it. It answers questions like "What were Gadget sales in the East?" without filters or pivot tables.

Syntax

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
sum_range
The numbers to add. In SUMIFS it comes first (in SUMIF it comes last).
criteria_range1
The column to test.
criterion1
The condition: a value like "East" or a comparison like ">100".

The practice data

An orders table: who sold what, where, and for how much. Every exercise below uses this sheet, and your answer goes in H2.

ABCDEF
1RepRegionProductUnitsPriceSales
2AnaEastWidget12560
3BenWestGadget520100
4CaraEastGadget820160
5DevNorthWidget205100
6AnaWestGizmo350150
7BenEastWidget7535
8CaraNorthGizmo250100
9DevEastGadget620120

Worked example

=SUMIFS(F2:F9,B2:B9,"East",C2:C9,"Gadget")

Adds the Sales of rows where Region is East and Product is Gadget. Result:

280

Your turn: 3 exercises

Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.

1 Return the total Sales for rep Ana.

H2

2 Return the total Units of Widget sold in the East.

H2

3 Return the total Sales of orders over 100.

H2

Master SUMIFS in the full course

Lesson 6 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.

Start free See pricing

Common SUMIFS mistakes

FAQ

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last. SUMIFS takes any number of conditions and puts the sum range first.

Can SUMIFS use OR logic?

Not directly. Add two SUMIFS together, for example East plus West.

Keep practicing