SUMIFS and COUNTIFS for quick reports

TL;DR:

• SUMIFS adds numbers where every listed condition is true. COUNTIFS counts the rows where they are.

• In SUMIFS the sum range comes first. Every range must be the same size.

• Wildcards such as * match part of a text value, and cell references make a report reusable.

You have a long sales list and need a small report: how much did each rep sell in each month, and how many orders was that? You do not need a pivot table for that. Two formulas, SUMIFS and COUNTIFS, can fill a small report grid that you design yourself, and the grid will update as the list grows.

What they do

SUMIFS adds up the numbers in one column, but only for rows where every condition you give is true. COUNTIFS counts how many rows meet all its conditions. Each condition is a pair: a range to look in, and a criterion to look for.

=SUMIFS(sum_range, criteria_range1, criterion1, criteria_range2, criterion2)
=COUNTIFS(criteria_range1, criterion1, criteria_range2, criterion2)

Notice the order. In SUMIFS, the range to add comes first. In the older single-condition SUMIF, it comes last, which trips people up. SUMIFS and COUNTIFS exist in both Excel and Google Sheets.

Multiple criteria, and the equal-size rule

With multiple criteria, all of them must be true at once, like AND. If you want either-or, add two SUMIFS together.

Every range must be the same size and shape. If the sum range is C2:C9 and a criteria range is A2:A8, you get an error or a wrong result, because the app lines the rows up one to one. The simplest habit is to select the same rows in each range, or use the same whole columns.

Criteria can be a typed value like "Ana", a cell reference like F2, or a comparison in quotation marks such as ">=100". To compare against a cell, join the pieces: ">="&G1.

Build the report in four moves

1. Check the list: One header row, no blank rows, numbers stored as numbers, and consistent spelling for reps and months.

2. Draw the report grid: Put the reps down column F and the months across row 1 starting at G. These cells will be your criteria.

3. Write the first formula with mixed references: Lock the criteria ranges and use $ so the formula can be copied across and down.

4. Copy it across the grid, then check totals: The grid total must equal a plain SUM of the sales column.

Worked example: sales by rep and month

Here is the list, with Rep in column A, Month in B and Amount in C, rows 2 to 9.

The report grid has reps in F2 (Ana) and F3 (Ben), months in G1 (Jan) and H1 (Feb). The formula in G2 is:

=SUMIFS($C$2:$C$9, $A$2:$A$9, $F2, $B$2:$B$9, G$1)

The dollar signs lock the ranges, while $F2 locks the column of the rep and G$1 locks the row of the month. Copy it into the four grid cells and each looks at its own rep and month.

Check by hand. Ana in January: 400 + 250 = 650. Ana in February: 300 + 150 = 450. Ben in January: 500 + 100 = 600. Ben in February: 200 + 350 = 550. The grid adds up to 650 + 450 + 600 + 550 = 2250, and the plain sum of the Amount column is 400 + 250 + 300 + 500 + 200 + 350 + 150 + 100 = 2250. They agree, so no row was missed or counted twice.

For the number of orders, change SUMIFS to COUNTIFS and drop the sum range: =COUNTIFS($A$2:$A$9, $F2, $B$2:$B$9, G$1). Ana in January gives 2 orders, Ben in February gives 2.

Wildcards

In criteria, * stands for any run of characters (including none) and ? for exactly one. So "A*" matches any rep whose name begins with A. =COUNTIF($A$2:$A$9, "A*") returns 4 here, since Ana has four rows. A wildcard on both sides, "*east*", matches any value containing east.

To find a real asterisk or question mark, put a tilde in front: "~*". Wildcards work on text only, not on numbers.

Mistakes to avoid

• Ranges of different sizes. One range with an extra row gives an error or a wrong total.

• Numbers stored as text. They are skipped by the sum. Check the cell alignment: text usually sits on the left.

• Hidden spaces. "Ana " is not "Ana". Clean the list with TRIM first.

• Months as dates. If the Month column holds real dates, a criterion of "Jan" will not match. Use a date range with ">=" and "<" conditions, or add a helper month column.

• Typing criteria into the formula. Using cell references makes the report easy to change.

When to pick a pivot table instead

A formula grid gives you control over layout and updates itself. A pivot table is faster for exploring, when you do not yet know which cuts you want. Many people explore with a pivot table and then build a SUMIFS grid for the report they send every week. If you get stuck on a formula, you can press DuckyHelper's key and ask out loud while it looks at your sheet.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF takes one condition and puts the sum range last. SUMIFS takes any number of conditions and puts the sum range first.

Can SUMIFS use OR?

Not directly. Add two SUMIFS together, one for each option.