COUNTIFS vs SUMPRODUCT for Counting With Conditions

The short answer to COUNTIFS vs SUMPRODUCT: use COUNTIFS for most counts with conditions, because it is fast, readable and handles several AND conditions and wildcards. Switch to SUMPRODUCT when COUNTIFS can't express the test: OR logic in one formula, comparing two columns row by row, or checking a calculated value such as the month of a date. This comparison is for anyone building KPI reports or facing an Excel test that asks for a tricky count.

COUNTIFS vs SUMPRODUCT at a glance#

COUNTIFS is the specialist for criteria-based counting; SUMPRODUCT is the flexible all-rounder.

How COUNTIFS counts#

COUNTIFS takes pairs of a range and a criterion, and counts the rows where every pair matches.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Criteria can be text ("North"), a comparison in quotes (">=3"), a cell (G1), an operator joined to a cell (">="&G2) or a wildcard pattern ("K*"). It is the natural next step after COUNTIF; see COUNTIF vs COUNTIFS. The COUNTIFS function guide lists every criterion type.

How SUMPRODUCT counts#

SUMPRODUCT multiplies arrays together and adds the results. For counting, you give it tests that return TRUE or FALSE for every row.

=SUMPRODUCT((A2:A9="North")*(D2:D9>=3))

Each bracket produces a list of TRUE and FALSE values. Multiplying turns them into 1s and 0s, and a row scores 1 only when both tests are TRUE. SUMPRODUCT then adds the 1s. Multiplying works as AND; adding with + works as OR. For a single test, use a double minus to convert TRUE and FALSE to numbers: =SUMPRODUCT(--(A2:A9="North")). The SUMPRODUCT function guide and the article on SUMPRODUCT explain the logic step by step.

The same counts done both ways#

Here is a sales log with a target for each order:

Task 1: two AND conditions (North laptops)#

=COUNTIFS(A2:A9,"North",C2:C9,"Laptop")
=SUMPRODUCT((A2:A9="North")*(C2:C9="Laptop"))

Both return 2 (rows 2 and 6). COUNTIFS is shorter and clearer.

Task 2: OR logic (North or South, 3 or more units)#

=COUNTIFS(A2:A9,"North",D2:D9,">=3")+COUNTIFS(A2:A9,"South",D2:D9,">=3")
=SUMPRODUCT(((A2:A9="North")+(A2:A9="South"))*(D2:D9>=3))

Both return 3: rows 2, 9 and 3. COUNTIFS needs one formula per option; SUMPRODUCT puts the OR inside one formula. With two options, either is fine. With five, SUMPRODUCT stays tidier.

Task 3: compare two columns (orders that beat their target)#

=SUMPRODUCT(--(D2:D9>E2:E9))

The result is 4 (rows 2, 3, 8 and 9). COUNTIFS can't do this, because each criterion is a single value, not a different value for every row.

Task 4: wildcards (reps whose name starts with K)#

=COUNTIFS(B2:B9,"K*")
=SUMPRODUCT(--(LEFT(B2:B9,1)="K"))

Both return 3. COUNTIFS supports the * wildcard directly. SUMPRODUCT doesn't, so you test the first letter with LEFT instead.

Task 5: a calculated condition (closed tickets opened in March)#

With dates in B2:B7 and status in C2:C7, as in the exercise on this page:

=SUMPRODUCT((MONTH(B2:B7)=3)*(C2:C7="Closed"))
=COUNTIFS(B2:B7,">="&DATE(2024,3,1),B2:B7,"<="&DATE(2024,3,31),C2:C7,"Closed")

Both return 3. COUNTIFS can't take MONTH(B2:B7) as a range, so it needs a start and end date instead. The SUMPRODUCT version is shorter but counts March in every year, so add *(YEAR(B2:B7)=2024) if the data spans several years.

Task 6: a case-sensitive count#

COUNTIFS ignores upper and lower case, so =COUNTIFS(A2:A9,"north") still returns 4. When case matters, such as product codes where ab-1 and AB-1 are different items, only SUMPRODUCT can help:

=SUMPRODUCT(--EXACT(A2:A9,"North"))

EXACT compares each cell with "North", including case, and returns TRUE or FALSE. The double minus turns those into 1s and 0s, and SUMPRODUCT adds them. On this data the result is still 4, because every entry is typed the same way, but a stray "NORTH" would no longer be counted.

Reading SUMPRODUCT formulas step by step#

If a SUMPRODUCT count looks wrong, test each bracket on its own. Select a blank cell and type =A2:A9="North" in Excel 2021 or Microsoft 365: it spills a column of TRUE and FALSE values, one per row, so you can see exactly which rows pass. Do the same for each test, then multiply them. In older versions, select part of the formula in the formula bar and press F9 to see its result, then press Esc so you don't replace the formula with the values.

When to use COUNTIFS#

Use COUNTIFS as your default for counting with conditions. It fits:

• AND conditions on several columns, such as region, product and status.
• Number and date ranges, using ">=" and "<=" criteria.
• Wildcard matches, such as names that start or end with certain text.
• Large data sets and dashboards with many formulas, where speed matters.
• Shared files, where colleagues need to read and edit the formula.

The COUNTIFS and SUMIFS guide shows how the same pattern sums values too.

When to use SUMPRODUCT#

Use SUMPRODUCT when the test goes beyond simple criteria. It fits:

• OR logic in one formula, especially with several options.
• Row-by-row comparisons, such as actual vs target or end date vs due date.
• Tests on calculated values, such as MONTH, YEAR, WEEKDAY, LEFT or LEN of a column.
• Case-sensitive counts, using --EXACT(A2:A9,"ABC").
• Weighted totals, such as units × price, where SUMPRODUCT's original job shines. See SUMPRODUCT vs SUMIFS.

In Microsoft 365, =SUM((A2:A9="North")*(C2:C9="Laptop")) works the same way, because dynamic arrays handle the array maths. SUMPRODUCT remains the safer choice for files opened in older versions.

Common mistakes with COUNTIFS and SUMPRODUCT#

1. Using commas instead of * in SUMPRODUCT tests. =SUMPRODUCT(A2:A9="North",C2:C9="Laptop") returns 0, because TRUE and FALSE values aren't converted. Multiply the tests, or add -- to each.
2. Ranges of different sizes. Both functions return #VALUE! if one range is A2:A9 and another is C2:C10.
3. Using wildcards in SUMPRODUCT. (B2:B9="K*") looks for the literal text K*. Use LEFT or SEARCH.
4. Passing a calculation to COUNTIFS. =COUNTIFS(MONTH(B2:B7),3) is rejected. Use date criteria or SUMPRODUCT.
5. Double counting with OR. If a row can match both OR conditions, adding tests counts it twice. Wrap the sum in a test: ((test1+test2)>0).
6. Whole-column references in SUMPRODUCT. A:A makes it process over a million rows and can slow the workbook. Use exact ranges or a table.

Our recommendation#

Reach for COUNTIFS first. It handles most real reporting needs, it is fast on big data, and anyone can read it. Use SUMPRODUCT for the cases COUNTIFS can't handle: OR logic in one formula, comparing columns row by row, and tests on calculated values like the month of a date. In KPI sheets, mixing both is normal; see the KPI summary guide for a worked example.

In short: COUNTIFS for criteria, SUMPRODUCT for logic that criteria can't express.

Practise it#

Try the exercise on this page: count closed tickets opened in March with SUMPRODUCT and MONTH. Then practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula, and it checks your answer instantly.