Conditional sums, counts and averages · B03

Before you start

You will be able to

Write SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS formulas that answer exactly the question asked.
Construct criteria with comparison operators, cell references, wildcards and blanks, including a date window made of two criteria.
Combine conditions with AND logic (several criteria) and OR logic (adding conditional sums, or SUMPRODUCT), without counting any row twice.
Calculate weighted totals and averages, and conditional totals, with SUMPRODUCT.
Build a two-way summary table with mixed references so that one formula fills it, and reconcile it with the data’s grand total.

Review first

Cells, references and formulas — Summary tables depend on absolute and mixed references.
Functions, logic, text and dates — You need function syntax, logical tests and the date functions EDATE and EOMONTH.
Summarising data: changes, averages and spread — Totals, counts and averages by group, and the weighted average, are what these functions calculate.

Fictional case A fictional coffee business founded by Ana Lim. It starts as a catering and barista-training service, opens its own cafés and an online shop for beans, and plans a canned cold brew.

The idea

Fictional case

Lantern’s 231 spring orders are worth $9,096.15. Ana now needs that total broken down. How much came through the web shop and how much through the app? How many orders arrived in April?

What did each month bring in, by channel? Which regions rate Lantern’s coffee highest? Each answer is a sum, count or average over only the rows that meet a condition, and Excel has a family of functions for exactly that. In this lesson the order sheet has nine columns: A order_date, B region, C product, D quantity, E unit_price, F order_total, G channel, H subscription and I rating, with the 231 orders in rows 2 to 232; the exercises keep only the columns each question needs, and say which.

Totals with conditions

A conditional aggregate is a sum, count, average, maximum or minimum taken over only the rows that meet one or more conditions. Each condition is a criterion applied to a criteria range: “the channel column equals web”, “the date column is on or after 1 April”. Excel checks every row, keeps the rows that pass every criterion, and aggregates the values in those rows.

SUMIF takes one condition: SUMIF(range,criteria,[sum_range]). If Lantern’s channels are in G2:G232 and order totals in F2:F232, =SUMIF(G2:G232,"web",F2:F232) returns $5,439.95, the value of the 132 web orders. If you leave out the sum range, SUMIF adds the cells in the criteria range itself, which is useful for =SUMIF(F2:F232,">=100"), the value of orders of $100 or more.

SUMIFS takes as many conditions as you need, up to 127 range and criteria pairs: SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…). Note the order. Microsoft points out that the sum range is the first argument of SUMIFS but the third of SUMIF, and swapping them is a common source of problems. Use SUMIFS even for one condition, so that the argument order is always the same; SUMIFS also refuses ranges of different sizes with #VALUE!, where SUMIF quietly accepts them (Microsoft).

How SUMIFS works on six orders. Each criterion range is tested row by row; only the rows that pass both tests (web and a subscription) contribute their order total, 15.30 + 13.95 = 29.25.

The rest of the family follows the same pattern. COUNTIF(range,criteria) and COUNTIFS(criteria_range1,criteria1,…) count the rows that pass: =COUNTIF(G2:G232,"web") is 132. AVERAGEIF and AVERAGEIFS average the values in the rows that pass, and return #DIV/0! if no row does. MAXIFS and MINIFS return the largest and smallest value that passes, and 0 if no row does; they are available in Excel 2019 and later and in Microsoft 365. In every function with several pairs, each criteria range must be the same size and shape as the range being summed, averaged or searched, or the result is #VALUE!.

Writing criteria

A criterion can be a number, a piece of text, a cell reference or an expression with a comparison operator. Microsoft’s examples are 32, ">32", B4, "apples" and "32". The rules that trip people up:

Operators go inside the quotes. ">=50" means “50 or more”; "<>web" means “anything but web”. A bare >=50 is a syntax error.
A cell reference with an operator is joined on with &. To compare with the date in K1, write ">="&K1. Written as ">=K1", the criterion is the text “>=K1”, which no date matches, and the result is quietly 0.
Text criteria ignore case. Microsoft notes that COUNTIF ignores upper and lower case, so "WEB", "Web" and "web" all match the same orders. They don’t ignore stray spaces: “web ” with a trailing space doesn’t match "web", which is one more reason for B02’s cleaning.
Wildcards match patterns in text. ? matches any single character and * any run of characters, so "House Blend*" matches both House Blend sizes and "*1kg" every 1 kg bag. A tilde before a wildcard makes it literal: "~*" matches an actual asterisk.
Blanks have their own criteria. "" matches empty cells and "<>" (not equal to nothing) matches cells that aren’t empty.
Numbers and text don’t mix. SUMIFS adds only numbers, so an order total stored as text is skipped, and ">=50" never matches text, even text that looks like 58.00. Convert such columns first (B02).
An empty criterion cell counts as 0. If a criterion refers to an empty cell, Microsoft notes that COUNTIFS treats it as the value 0, not as “blank”; use "" when you mean blank.
Very long text fails. Microsoft warns that COUNTIF and SUMIF give wrong results when matching strings longer than 255 characters.

With these, Ana’s product questions are one formula each. =SUMIF(C2:C232,"House Blend*",D2:D232) adds the quantity column for both House Blend products: 202 bags in 49 orders. =COUNTIF(C2:C232,"*1kg") counts the 29 orders of 1 kg bags. And on the ratings, =COUNTIFS(G2:G232,"web",I2:I232,"<>") counts the 84 web orders that were rated.

In depth

Date windows

Dates are numbers, so a period is two criteria on the same date column: on or after the first day, and before the first day of the next period. With 1 April 2026 in K1 and 1 May 2026 in K2, April’s orders are =COUNTIFS(A2:A232,">="&K1,A2:A232,"<"&K2): 63. Using “before the next start” rather than “on or before the last day” means the window never needs to know how long the month is, and it stays right if the data ever carries times of day as well as dates.

The boundary is where windows go wrong. Write "<="&K2 instead of "<"&K2 and April gains the 4 orders placed on 1 May. Write the dates as text, ">=1/4/2026", and the criterion depends on the computer’s regional settings: with US settings it means 4 January. Keep the dates in cells, or build them with DATE: ">="&DATE(2026,4,1). To step through months in a table, put each month’s first day in a cell and let EDATE find the next: "<"&EDATE(K3,1).

AND and OR

Criteria in SUMIFS, COUNTIFS and AVERAGEIFS are combined with AND: a row counts only if it passes all of them. That is usually what you want (“web orders in April”), but it means two criteria on the same column must both be true of one cell. =SUMIFS(F2:F232,B2:B232,"North",B2:B232,"South") asks for orders whose region is both North and South, and returns 0.

For OR, add one conditional sum per alternative: =SUMIFS(F2:F232,B2:B232,"North")+SUMIFS(F2:F232,B2:B232,"South") is $3,192.25. Adding is safe only when no row can meet both conditions, as here, because an order has one region. When the conditions overlap, adding counts the overlap twice. The value of orders that came through the web or were subscriptions is not web ($5,439.95) plus subscriptions ($2,977.65) = $8,417.60, because the $1,273.95 of web subscription orders is in both; the right answer is $7,143.65. Either subtract the overlap with a third SUMIFS, or use SUMPRODUCT.

SUMPRODUCT

SUMPRODUCT(array1,[array2],…) multiplies its ranges element by element and adds the products. Its first use is weighted totals: =SUMPRODUCT(D2:D232,E2:E232) multiplies each order’s quantity by its unit price and adds the 231 products, giving Lantern’s spring sales before discounts, $9,685.00. Take off the $588.85 of discounts and you are back at $9,096.15, a useful reconciliation.

Divided by the number of bags, the same sum is a weighted average price: =SUMPRODUCT(D2:D232,E2:E232)/SUM(D2:D232) is $9685.00 \div 477 \approx 20.30$ dollars per bag. The plain =AVERAGE(E2:E232) is $22.34, because it gives an order of one Gift Box Trio the same weight as the order of 120 House Blend bags. Which is right depends on the question: the average price paid per bag is the weighted one.

SUMPRODUCT also takes conditions. A comparison on a range, such as (G2:G232="web"), gives a list of TRUE and FALSE values; arithmetic turns them into 1s and 0s, so =SUMPRODUCT((G2:G232="web")*F2:F232) adds the web orders’ totals. Microsoft notes that SUMPRODUCT treats any non-numeric entry as zero, which is why the multiplication matters: =SUMPRODUCT((G2:G232="web"),F2:F232), with a comma, multiplies TRUE and FALSE values that count as zero and returns 0. Two more of Microsoft’s rules: the arrays must have the same dimensions (otherwise #VALUE!), and whole-column references make SUMPRODUCT multiply every cell in the column, so give it exact ranges.

OR conditions that overlap are where SUMPRODUCT earns its place. Add the tests and keep the rows where the sum is above zero: =SUMPRODUCT((((G2:G232="web")+(H2:H232="yes"))>0)*F2:F232) returns $7,143.65, counting each web subscription order once.

Summary tables with one formula

A two-way summary table puts one category down the side and another across the top, and every cell is a conditional aggregate of the pair. Built with mixed references, one formula fills the whole table. For value by month and channel, put each month’s first day in K2:K5 and the channels in L1:M1. Each cell needs “this row’s month”, so the month reference fixes its column ($K2), and “this column’s channel”, so the channel reference fixes its row (L$1); the data ranges are fixed in both directions. In L2:

=SUMIFS($F$2:$F$232,$A$2:$A$232,">="&$K2,$A$2:$A$232,"<"&EDATE($K2,1),$G$2:$G$232,L$1)

Then check the table against the data. Add a total row and a total column with SUM; the grand total must equal =SUM(F2:F232), each row total must equal that month’s value, and each column total that channel’s. If the grand total falls short, a row of data is outside every cell of the table: a date outside the four months, or a channel spelled “Web ” with a space. A count table should likewise add up to the number of orders. The challenging example builds and checks the table.

A PivotTable (Module 2) produces the same table by dragging fields, and is faster to explore with. Formulas have advantages of their own: they update the moment the data changes, they can sit anywhere in a report, and each cell says exactly what it adds. The pivot-tables lesson compares the two.

Formulas

Conditional sum (SUMIFS)

S = \sum_{i=1}^{n} x_i\, c_i, \qquad c_i = \begin{cases} 1 & \text{if row } i \text{ meets every criterion} \\ 0 & \text{otherwise} \end{cases}

$S$: The conditional sumas x
$n$: Number of rows in the datarows
$i$: The row number, from 1 to n
$x_i$: The value in row i of the sum rangeas recorded
$c_i$: Whether row i passes all the criteria

COUNTIFS is the sum of c_i; AVERAGEIFS is S divided by that count (only rows whose value is a number).

Weighted average price

\bar{p}_w = \dfrac{\sum_i q_i\, p_i}{\sum_i q_i}

$\bar{p}_w$: Average price per item, weighted by quantity$ per item
$q_i$: Quantity in order iitems
$p_i$: Unit price in order i$ per item

In Excel: =SUMPRODUCT(D2:D232,E2:E232)/SUM(D2:D232).

Worked examples

A first example: Value, orders and average order value by channel

Fictional figures

Lantern’s channels are in G2:G232 and order totals in F2:F232. In K2 and K3 Ana has typed web and app. Find each channel’s order value, number of orders and average order value with formulas that fill down.

The channel summary (L, M and N filled down from row 2)
K: channel	L: value	M: orders	N: average order value
web	$5,439.95	132	$41.21
app	$3,656.20	99	$36.93

Value: =SUMIFS($F$2:$F$232,$G$2:$G$232,K2). The criterion is the cell K2, so the same formula filled to row 3 picks up app; the data ranges are absolute so that they don’t slide down.
Orders: =COUNTIFS($G$2:$G$232,K2).
Average order value: =L2/M2, or in one step =AVERAGEIFS($F$2:$F$232,$G$2:$G$232,K2), which gives the same result.
Reconcile: $5,439.95 + $3,656.20 = $9,096.15, the spring total, and 132 + 99 = 231 orders.

Answer. Web: $5,439.95 from 132 orders, $41.21 an order. App: $3,656.20 from 99 orders, $36.93 an order.

What it means. The web shop brings in more, and more per order, but one order explains much of the gap: the $1,512.00 office order LC-10144 came through the web. Without it the web average is $(5439.95 - 1512) \div 131 \approx 29.98$ dollars, below the app’s. A conditional average is still an average, and B02’s outlier still moves it.

A harder example: A month-by-channel table, built and checked

Fictional figures

Build Lantern’s spring order value by month (rows) and channel (columns) with one formula, and prove that the table accounts for every order. The first day of each month is in K2:K5 (1 March to 1 June 2026) and the channels in L1:M1, as in the Explore worksheet below.

Order value by month and channel, spring 2026
K: month starting	L: web	M: app	N: total
March	$1,065.10	$910.75	$1,975.85
April	$1,162.90	$1,012.65	$2,175.55
May	$2,159.05	$770.70	$2,929.75
June	$1,052.90	$962.10	$2,015.00
Total	$5,439.95	$3,656.20	$9,096.15

Decide what each cell needs: orders in this row’s month and this column’s channel. The month is a window, so it takes two criteria on the date column: on or after K2, and before EDATE(K2,1), the first of the next month.
Fix the right parts. The data ranges never move: $F$2:$F$232, $A$2:$A$232, $G$2:$G$232. The month is always in column K ($K2), the channel always in row 1 (L$1). In L2: =SUMIFS($F$2:$F$232,$A$2:$A$232,">="&$K2,$A$2:$A$232,"<"&EDATE($K2,1),$G$2:$G$232,L$1), filled across to M2 and down to M5.
Read one filled cell to check the references: M4 becomes =SUMIFS($F$2:$F$232,$A$2:$A$232,">="&$K4,$A$2:$A$232,"<"&EDATE($K4,1),$G$2:$G$232,M$1), app orders in May: $770.70.
Add totals with SUM and reconcile. The row totals ($1,975.85, $2,175.55, $2,929.75, $2,015.00) match each month’s value, the column totals match each channel, and the grand total is $9,096.15, the same as =SUM(F2:F232). Every order is in exactly one cell.

Answer. One formula, typed in L2 and filled to M5, gives the eight values in the table; the totals reconcile to the spring total of $9,096.15.

What it means. May stands out, with web sales of $2,159.05, and the bulk office order of $1,512.00 on 14 May is most of the reason; without it, May’s web value would be $647.05. The table says where the money came from; it takes a look at the rows to say why. Because the table is formulas, July’s orders pasted below the data (with the ranges extended) and a fifth month row would fill in the same way.

Applied: a business decision: Where are customers least satisfied?

Fictional figures

Lantern’s owner asks which region Ana should look at first if she wants to improve customer satisfaction. Ratings (1 to 5) are in I2:I232, blank when the customer didn’t rate; regions are in B2:B232, and the five region names in K2:K6.

Ratings by region
K: region	L: orders	M: rated	N: average rating
North	58	35	3.83
South	43	27	3.96
East	52	40	4.33
West	53	33	3.97
Central	25	19	4.58

Orders per region: =COUNTIFS($B$2:$B$232,K2).
Rated orders: add a criterion on the rating column, =COUNTIFS($B$2:$B$232,K2,$I$2:$I$232,"<>"). The "<>" criterion keeps only the cells that aren’t empty.
Average rating: =AVERAGEIFS($I$2:$I$232,$B$2:$B$232,K2). AVERAGEIFS uses only rows whose rating is a number, so unrated orders are left out of both the sum and the count. Dividing a SUMIFS by the region’s order count instead would treat every unrated order as a zero.
Read the table with its counts. North has the lowest average, 3.83, from 35 ratings; Central the highest, 4.58, but from only 19. Sort or rank the averages only after checking how many ratings stand behind each.

Answer. North (3.83 from 35 ratings) is the place to start; Central (4.58) is highest but rests on the fewest ratings.

What it means. The averages differ by less than one point on a five-point scale and some rest on a few dozen ratings, so they are a reason to look, not a finding: Ana reads the North comments and checks its delivery times before drawing conclusions. Whether a gap this size is more than chance is a question for B04’s statistics, not for SUMIFS.

Explore

Common mistakes

“SUMIFS takes its arguments in the same order as SUMIF.”
The sum range comes first in SUMIFS and last in SUMIF. =SUMIFS(G2:G232,"web",F2:F232) treats “web” as a range and fails. Pick one function (SUMIFS works for one condition too) and use it consistently.
“A cell reference inside the quotes works.”
">=K1" is the text “>=K1”; no date matches it and the result is a quiet 0. Join the operator to the reference: ">="&K1.
“Two criteria on the same column give OR.”
SUMIFS criteria are combined with AND, so "North" and "South" on the region column return 0. For OR, add two SUMIFS (when the conditions can’t overlap) or use SUMPRODUCT.
“Adding the subtotals is fine for OR.”
Only when no row meets both conditions. Web orders plus subscription orders counts the web subscription orders twice. Subtract the overlap, or use SUMPRODUCT with (…+…)>0.
“A month is everything up to the last day.”
Writing "<="&K2 with K2 the first day of the next month adds that day’s orders to this month. Use “on or after the start, before the next start”, with EDATE for the next start.
“An average by group is SUMIFS divided by COUNTIF.”
Only if every row in the group has a value. With blanks (unrated orders), divide by a COUNTIFS that also requires "<>", or use AVERAGEIFS, which skips non-numeric cells by itself.

Guided practice

Work through these with hints when you need them. Every hint and every worked solution is there to be used.

Loading four questions…

On your own

No hints this time. Answer, check, and read the feedback; the solution appears once you have answered or if you ask for it. Your first answers count toward mastery.

Loading six questions…

In practice

Ana’s monthly pack. Ana’s report to Lantern’s owner opens with three small tables, all formulas on the order data: value and orders by month and channel, value by region with each region’s share of the total, and ratings by region with their counts. Each table has its check, the grand total against =SUM() of the data and the order count against 231, in a cell that shows “OK” or the difference. When June’s orders arrived, Ana pasted them below May’s, extended the ranges, and the tables and their checks updated together.

Questions that are one formula. Most of the questions a manager asks about a transaction table are conditional aggregates: sales of a product line this quarter (SUMIFS with a wildcard and a date window), how many customers ordered more than once (a COUNTIF on each customer’s ID, then a count of the ones above 1), the largest order from each region (MAXIFS), the average delivery time for app orders (AVERAGEIFS). Knowing the family well means answering in minutes, and with a formula someone can check, rather than by filtering and copying numbers by hand.

Fictional case

Quillbrook Food Share, a fictional charity, logs every donation with its date, campaign, donor type and amount. Its treasurer reports donations by campaign and month with one SUMIFS table, and the number of first-time donors with COUNTIFS. One year the winter campaign’s total looked a third too high: the treasurer had added “online or corporate” donations with two SUMIFS, and corporate donations made online were in both. Rebuilt with SUMPRODUCT and an OR test, the table matched the bank statements, and the treasurer added a reconciliation row that has caught two more problems since.

Formulas or a PivotTable? For a report that is produced every month in the same shape, formula tables are easy to audit and to place exactly where they are needed. For exploring (by region, then by product, then by week), a PivotTable is faster, and Module 2 covers it. A good routine is to explore with a pivot, then build the final report from formulas that can be checked cell by cell.

Knowledge check

Six questions, one attempt each, no hints. Score 80% or more to complete the lesson; you can take it again at any time.

Loading six questions…

Summary

SUMIF(range, criteria, [sum_range]) handles one condition; SUMIFS(sum_range, criteria_range1, criteria1, …) handles up to 127, with the sum range first. COUNTIF(S), AVERAGEIF(S), MAXIFS and MINIFS follow the same pattern.
Criteria: operators inside quotes (">=50"), joined to cell references with & (">="&K1); text criteria ignore case; * and ? are wildcards and ~ makes them literal; "" matches blanks and "<>" non-blanks.
A date window is two criteria on the date column: on or after the start, before the next start. Keep the dates in cells or build them with DATE and EDATE.
Criteria within one SUMIFS are combined with AND. For OR, add separate SUMIFS only when the conditions can’t overlap; otherwise subtract the overlap or use SUMPRODUCT.
SUMPRODUCT multiplies ranges element by element and adds: weighted totals and averages, and conditions written as (range="value")*values. Non-numbers count as zero, ranges must be the same size, and whole columns are slow.
One formula with mixed references fills a two-way summary table; always reconcile the table’s totals with the data.

Terms introduced

Conditional aggregate: A sum, count, average, maximum or minimum taken over only the rows of a table that meet one or more conditions.
Criterion: A condition used by a function such as SUMIFS or COUNTIFS to decide which rows to include, such as "web", ">=50" or ">="&K1; plural criteria.
Criteria range: The range of cells that a criterion is tested against, row by row, in functions such as SUMIFS and COUNTIFS.
Wildcard character: A character that matches others in a text criterion: ? matches any one character and * any run of characters; a tilde (~) before either matches it literally.
Summary table: A table of aggregates for each combination of categories, such as order value by month and channel, often built with one formula filled across and down.

Sources

Microsoft Support. SUMIFS function. support.microsoft.com/en-us/excel/sumifs-function
Microsoft Support. SUMIF function. support.microsoft.com/en-US/Excel/sumif-function
Microsoft Support. COUNTIF function (criteria, wildcards and case). support.microsoft.com/en-us/office/countif-function-e0de10c6…
Microsoft Support. COUNTIFS function. support.microsoft.com/en-us/excel/countifs-function
Microsoft Support. AVERAGEIFS function. support.microsoft.com/en-US/Excel/averageifs-function
Microsoft Support. MAXIFS function. support.microsoft.com/en-US/excel/functions/maxifs-function
Microsoft Support. SUMPRODUCT function. support.microsoft.com/en-US/Excel/sumproduct-function

Before you start

You will be able to

Review first

The idea

Totals with conditions

Writing criteria

In depth

Date windows

AND and OR

SUMPRODUCT

Summary tables with one formula

Formulas

Conditional sum (SUMIFS)

Weighted average price

Worked examples

Explore

Common mistakes

“SUMIFS takes its arguments in the same order as SUMIF.”

“A cell reference inside the quotes works.”

“Two criteria on the same column give OR.”

“Adding the subtotals is fine for OR.”

“A month is everything up to the last day.”

“An average by group is SUMIFS divided by COUNTIF.”

Guided practice

On your own

In practice

Knowledge check

Summary

Key formulas

Terms introduced

Sources