Excel for Analysts · 13 min · 150 XP

SUMIFS, COUNTIFS and friends

Add up, count and average only the rows that match, including conditions you can change.

Most analysis questions sound like the total of X where Y. SUMIFS answers them: first the range to add up, then as many range, criteria pairs as you need. A row only counts if it meets every condition. COUNTIFS and AVERAGEIFS work the same way.

text
=SUMIFS(G2:G13, C2:C13, "Apparel")                  Apparel revenue
=SUMIFS(G2:G13, C2:C13, "Apparel", B2:B13, "Mar")   Apparel revenue in March
=COUNTIFS(B2:B13, "Feb")                            lines in February
=AVERAGEIFS(G2:G13, C2:C13, "Gear")                 average Gear line

Criteria are text with an optional operator: ">=100", "<>Gear", and "Wool*" (where * matches anything and ? matches one character). Text matching ignores case. To use a cell in a criterion, join the operator to it with &: ">="&J7. Writing ">=J7" compares against the letters J7.

Watch the argument order. SUMIFS puts the sum range first, but the older SUMIF puts it last: =SUMIF(C2:C13, "Apparel", G2:G13). Every range in one SUMIFS must also be the same size, or you get #VALUE!. Put the conditions in labelled cells, as this challenge does, and one formula answers the question for any category.

Loading your workspace…