Conditional Aggregation

Imagine a busy kirana shop at closing time. The owner does not want the total of every bill in the drawer — she wants the total of just the bills that follow a rule. Add up only the North sales. Count only the bills over ₹500. That is conditional aggregation: adding, counting or averaging only the rows that pass your test.

Picture SUMIFS as a smart cashier. You hand her a stack of bills and a short list of rules — North region, over ₹500, month of January — and she rings up only the bills that obey every rule. A bill that breaks even one rule is quietly set aside. Each rule is a filter, and a row must pass all of them to be counted.

The whole family works this way, and the name tells you the job it does:

• SUM… adds a column of ₹ amounts.
• COUNT… tallies how many rows match — no adding, just counting heads.
• AVERAGE… works out the mean of the matching rows.

And the ending tells you how many rules it takes: …IF handles one rule; …IFS (with the extra S) handles several. So SUMIF totals with one rule, SUMIFS totals with many, COUNTIFS counts with many, and so on across the family.

Two little words to learn. A range is the column you look in (say the Region column), and the criteria is the rule itself (say "North"). Give Excel the range and the rule, and it does the filtering for you — instantly, whether the sheet has ten rows or ten thousand.

Two quick pictures: a map of the whole family, then a decision tree for picking the right one.

Every function here follows one simple shape: tell Excel where to look, then what to look for. The only wrinkles are the argument order and how you spell out the rule. Let us take them one at a time, with a little sales sheet in mind.

SUMIF — one rule. Its shape is SUMIF(range, criteria, [sum_range]). The range is the column Excel checks, the criteria is the rule, and the optional sum_range is the column it actually adds up. If you leave out sum_range, Excel simply adds the very column it checked — ideal for =SUMIF(B2:B7,">40"), which totals the amounts that are over 40. When the rule lives in one column but the money lives in another, give all three: =SUMIF(A2:A7,"North",C2:C7) checks Region in column A and adds the ₹ from column C.

SUMIFS — many rules. Its shape is SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …). The column of ₹ you want totalled goes first; after that come pairs — a column to check, then the rule for it. Add as many pairs as you like. A row is added only if it passes every pair — that is AND logic. There is no OR hiding in here: each extra pair makes the filter stricter, never looser.

COUNTIF and COUNTIFS count rows instead of adding them, so there is no money column to fuss over. COUNTIF(range, criteria) answers "how many?" for one rule — =COUNTIF(A2:A9,"North") tells you how many North orders exist. COUNTIFS(range1, criteria1, range2, criteria2, …) takes several rules: =COUNTIFS(A2:A9,"North",C2:C9,">500") counts the North orders worth over ₹500.

AVERAGEIF and AVERAGEIFS return the mean of the matching rows. Watch the order quirk: AVERAGEIF(range, criteria, [average_range]) mirrors SUMIF (the check-range comes first), while AVERAGEIFS(average_range, range1, criteria1, …) mirrors SUMIFS (the averaged column comes first). So =AVERAGEIF(A2:A9,"North",C2:C9) gives the average North order value. If no row matches, the AVERAGE family returns a #DIV/0! error — there is simply nothing to divide.

MAXIFS and MINIFS round out the clan (Excel 2019 and Microsoft 365 onwards). They pick out the largest or smallest matching value and read exactly like SUMIFS: MAXIFS(max_range, range1, criteria1, …). For instance =MAXIFS(C2:C9,A2:A9,"North") returns the biggest single North sale, and MINIFS the smallest. Same pairs, same AND logic — just max or min instead of a total.

Writing the criteria. The rule is the fiddly part, so keep this cheat-sheet close. Text and comparison rules go inside double quotes; to compare against a value that lives in a cell, join the operator to the cell reference with an &.

Two wildcards do the heavy lifting. * stands for any number of characters — so "A*" catches every name starting with A, and "*ltd*" catches every firm with "ltd" anywhere in the name. ? stands for exactly one character, handy for fixed-length codes. The cell trick — ">"&E1 — is pure gold: park a threshold in a cell, and the whole report re-totals the instant you change that one number.

Put it together and a single column of formulas becomes a living dashboard. Here are four regions, each totalled with one SUMIFS:

Conditional aggregation is easy to write and easy to write wrongly — the sheet rarely warns you, it just returns a confidently wrong number. Here are the slips that catch nearly everyone, and the pro moves that set your work apart.

Type a real formula into the bar; the target cell fills in and turns green when your answer matches. Work top to bottom — each drill adds one new idea.