Skip to content

SheetRoutine

Spreadsheet tutorials for Excel, Google Sheets and LibreOffice. Each one ends with a working file, and every number in it was checked.

Summarise sales by category in Excel with SUMIFS, COUNTIFS, and AVERAGEIFS

A sales log answers three questions per category: how many orders, how much money, and what the typical order was worth. Three functions answer one question each, all from the same log, and all three take the same range-and-criteria pairs — so one summary table covers every category at once.

Checked
AppsExcel, Google Sheets, LibreOffice
VersionsLibreOffice 24.2.7.2 (recalculation) · Python 3.12.3 · openpyxl 3.1.2
Read time8 minutes

Measured result — from the recalculated file

Tools: 8 orders$556.16, average $69.52
Garden: 6 orders$463.80, average $77.30
Paint: 4 orders$429.50, average $107.38
All categories: 18 orders$1,449.46, average $80.53
Tools in the North: 4 orders$272.29, average $68.07
Tools in the South: 4 orders$283.87, average $70.97
Plumbing (no sales rows)0 orders, $0.00, average #DIV/0!

Download the spreadsheet: sales-summary-by-category.xlsx (6,994 bytes)

The file that was recalculated and checked. It contains the 18-row sales log and the summary table, including the Plumbing row with no sales.

1What you need

2The sales log

The “Sales” sheet holds 18 rows, 2 to 17 November 2026: date, region (North or South), category (Tools, Garden, or Paint), product, quantity, unit price, and amount. Amount is a formula, not a typed-in number:

G2  =E2*F2

Four claw hammers at $18.49 give $73.96; ten pairs of safety glasses at $6.49 give $64.90. Every formula in the summary reads the Amount column, so the summary can only be right if these rows are right — and they are checked row by row below.

3COUNTIFS counts the orders

COUNTIFS counts the number of cells in a range that meet multiple criteria (Statistical functions (reference) — Microsoft Support). One criterion is enough here — the category:

B2  =COUNTIFS(Sales!$C$2:$C$19, $A2)

For Tools the result is 8: eight of the eighteen rows are Tools. Garden gives 6, Paint gives 4, and the three counts add to 18, the number of rows in the log.

4SUMIFS totals the sales

SUMIFS adds the values in a range that meet the same criteria, but its first argument is the range to add (SUMIFS function — Microsoft Support):

C2  =SUMIFS(Sales!$G$2:$G$19, Sales!$C$2:$C$19, $A2)

For Tools the result is $556.16. Garden totals $463.80 and Paint $429.50. The three totals add to $1,449.46, which is also the sum of the Amount column itself — the summary and the log agree.

5AVERAGEIFS averages the order value

AVERAGEIFS returns the average of all cells that meet multiple criteria. Like SUMIFS, it takes the range to average first:

D2  =AVERAGEIFS(Sales!$G$2:$G$19, Sales!$C$2:$C$19, $A2)

For Tools the result is $69.52 — $556.16 across 8 orders. Note what this average is: the average value of one order row, not the average price of one item. Paint has the highest average order at $107.38, even though it has the fewest orders, because paint is sold in $28.50 to $48.00 cans rather than small items.

6Two conditions: Tools by region

Add a second range-and-criteria pair and the same three functions split a category by region. Tools in the North:

B8  =COUNTIFS(Sales!$C$2:$C$19, "Tools", Sales!$B$2:$B$19, "North")
C8  =SUMIFS(Sales!$G$2:$G$19, Sales!$C$2:$C$19, "Tools", Sales!$B$2:$B$19, "North")
D8  =AVERAGEIFS(Sales!$G$2:$G$19, Sales!$C$2:$C$19, "Tools", Sales!$B$2:$B$19, "North")

North: 4 orders, $272.29, average $68.07. South: 4 orders, $283.87, average $70.97. The two halves add back to the Tools row exactly — 8 orders and $556.16 — which is the check that the split lost no rows.

7The empty category

The summary has a fourth category, Plumbing, with no rows in the log. COUNTIFS returns 0 and SUMIFS returns $0.00, both correct. AVERAGEIFS has nothing to average, so it returns #DIV/0! — not zero. A summary that prints #DIV/0! looks broken, so the file wraps the average in IFERROR:

E2  =IFERROR(D2, "No sales")

The Plumbing row reads “No sales” while the other three rows show their numbers. If a Plumbing sale is added to the log later, the row fills in on its own.

8The totals row

The bottom row sums the category rows rather than re-running the functions over the log:

B6  =SUM(B2:B5)
C6  =SUM(C2:C5)
D6  =AVERAGE(Sales!$G$2:$G$19)

18 orders, $1,449.46, average $80.53 per order. The average here is the plain AVERAGE of the Amount column — the same value as total divided by count, $1,449.46 ÷ 18.

Traps

AVERAGEIFS with no matches is #DIV/0!, not 0. A category with no rows has no average. Wrap it in IFERROR if the table is meant to be read, or the error spreads to anything that reads the cell.

The average is per order row, not per item. AVERAGEIFS over the Amount column averages whole orders. Ten pairs of safety glasses in one row count once, at $64.90 — the quantity inside the row does not change the count.

Ranges must be the same size. The criteria range and the range being summed or averaged must cover the same rows. A sum range of G2:G19 with a criteria range of C2:C18 drops the last sale.

SUMIFS takes the sum range first; COUNTIFS takes none. The three functions look alike but their first arguments differ: SUMIFS and AVERAGEIFS start with the range to calculate, COUNTIFS starts with a criteria range. Mixing them up is the most common mistake in a summary table.

How this was checked

The workbook was built with openpyxl 3.1.2 (formulas only, no typed-in results), recalculated by LibreOffice 24.2.7.2 in headless mode, and read back with openpyxl in data-only mode. The expected values were computed independently in Python 3.12.3 from the same 18 sales rows: all 18 amounts, every category count, total, and average, the Plumbing #DIV/0! and its IFERROR text, the grand totals, and both regional splits matched within $0.01.

Sources