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.
=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…