SUMIFS in Excel: add rows that match two or more conditions

SUMIFS is SUMIF with more than one condition. Ana’s sales in March. Orders between two dates over 500.

The formula

=SUMIFS(what_to_add, range1, condition1, range2, condition2, ...)

Note the order: the column to add comes first in SUMIFS, unlike SUMIF.

What you will make

Ana’s total for March only.

Steps

1. Names in A, months in B, amounts in C.
2. Click E1.
3. Type =SUMIFS(C:C, A:A, "Ana", B:B, "Mar"), press Enter.

Result: 100. Only row 1 passes both tests.

Between two dates

=SUMIFS(C:C, D:D, ">=" & F1, D:D, "<=" & F2)

Dates in column D, start date in F1, end date in F2. The & glues the sign to the cell.

Over a number and one name

=SUMIFS(C:C, A:A, "Ana", C:C, ">60")

Check it worked

Filter A to Ana and B to Mar. The Sum in the status bar should equal your SUMIFS.

Common mistake

Ranges of different sizes. A1:A100 with C1:C90 gives #VALUE!. Use whole columns (A:A, C:C) or matching rows.