SUMIF and SUMIFS

The same sales sheet as the COUNTIF lesson, with Raj's amount in D6 left empty:

SUMIF

=SUMIF(B2:B8, "North", D2:D8)    → 2600    1200 + 450 + 950
=SUMIF(D2:D8, ">1000")           → 5100    1200 + 2300 + 1600

=SUMIF(range_to_check, criteria, range_to_add)

Excel checks each cell in the first range, and for every match adds the cell in the same row of the third range. When the cells you check are the cells you add, as in the second example, leave the third argument out.

The criteria work exactly as in COUNTIF: ">1000", "<>East", "A*", or ">"&F1 for a value in a cell.

SUMIFS: several conditions

=SUMIFS(D2:D8, B2:B8, "North", C2:C8, "Asha")    → 2150    1200 + 950

SUMIFS adds the values where every condition is true: here, Asha's sales in the North.

Between two dates

=SUMIFS(D2:D8, A2:A8, ">="&DATE(2024,6,10), A2:A8, "<"&DATE(2024,6,20))    → 2750

Two conditions on the same column make a range: on or after 10 June, and before 20 June. That's 450 + 2300, with Raj's empty cell on 18 June counting as nothing. Build dates with DATE(year, month, day) rather than typing them inside the quotes, which avoids confusion between day-month and month-day order.

AVERAGEIF, MAXIFS and MINIFS

=AVERAGEIF(B2:B8, "East", D2:D8)     → 1950    (2300 + 1600) / 2
=MAXIFS(D2:D8, B2:B8, "South")       → 800     the empty cell is ignored
=MINIFS(D2:D8, B2:B8, "North")       → 450

AVERAGEIF and AVERAGEIFS follow the same pattern as their SUM cousins. MAXIFS and MINIFS (Excel 2019 and later) take the value range first, like SUMIFS.

A summary table

Combine the functions with references, and one formula fills a whole report:

The formula in G2 takes the region from F2, and the data ranges are locked with $, so filling it down to G4 gives every region's total. This is a hand-made version of what a PivotTable does automatically.