COUNTIF、COUNTIFS 與 SUMPRODUCT：何時使用哪個？

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• COUNTIF and SUMPRODUCT: every Excel version and Google Sheets.
• COUNTIFS: Google Sheets, and every Excel version Microsoft’s page lists (Excel 2016 through 365, plus Excel for the web).
• Array constants like {"East","West"} work in Excel. In Google Sheets they may need ARRAYFORMULA (see below).
• If your region uses commas as decimal separators, use semicolons between arguments.

Example data

Put this in A1:F9:

The lowercase “ann” in row 4 and the asterisk in “Gizmo*” (row 8) are there on purpose. Both trip up COUNTIF.

Decision table

*In Excel 365, =SUM(--(D2:D9>E2:E9)) also works without SUMPRODUCT, because dynamic arrays evaluate the comparison natively.

COUNTIF: one condition

=COUNTIF(A2:A9,"East")

Returns 4. A few criteria patterns cover most needs:

Note the "G*" result: it counts three Gadgets and “Gizmo*”. Microsoft’s documentation explains that ? and * are wildcards in COUNTIF criteria and a tilde (~) in front makes them literal.

OR in the same column: COUNTIF has one condition, so add two:

=COUNTIF(A2:A9,"East")+COUNTIF(A2:A9,"West")

Returns 6. A shorter version with an array constant:

=SUM(COUNTIF(A2:A9,{"East","West"}))

COUNTIFS: all conditions must be true

=COUNTIFS(A2:A9,"East",D2:D9,">500")

Returns 3 (600, 900 and 1200). Microsoft documents up to 127 range/criteria pairs, and each range must have the same number of rows and columns as the first one. Otherwise you get #VALUE!.

Between two values (same column twice):

=COUNTIFS(D2:D9,">=300",D2:D9,"<=700")

Dates in a month: don’t try to test MONTH(). Give COUNTIFS a start and an end date:

=COUNTIFS(F2:F9,">="&DATE(2026,3,1),F2:F9,"<"&DATE(2026,4,1))

Returns 3 (March 2, 15 and 28). Using < the first day of the next month avoids the classic end-of-month bug where <=March 31 misses timestamps later that day.

Blank criteria trap: Microsoft’s documentation says that if a criteria argument points to an empty cell, COUNTIFS treats it as 0. A dashboard with an empty “Region” filter cell doesn’t mean “all regions”. It means “Region equals 0”, which usually counts nothing. Handle it explicitly:

=IF(H1="",COUNTIFS(D2:D9,">500"),COUNTIFS(A2:A9,H1,D2:D9,">500"))

Or use a wildcard so an empty H1 matches any text: =COUNTIFS(A2:A9,H1&"*",D2:D9,">500"). Be careful with this one: “East” would then also match “Eastern”.

SUMPRODUCT: anything you can express as TRUE/FALSE

SUMPRODUCT multiplies arrays and adds the results. Microsoft’s documentation notes that it treats non-numeric entries as zero. That matters, because a comparison returns TRUE/FALSE, not 1/0. The double minus (--) converts TRUE/FALSE to 1/0 so they’re counted:

=SUMPRODUCT(--(A2:A9="East"))

In Excel, use -- to coerce the Boolean array. Sheets returned 4 even without it in the live check; keeping the coercion makes the intent portable. Here’s what SUMPRODUCT can do that the COUNTIF family can’t.

OR across different columns

Count orders that are East or Widget:

=SUMPRODUCT(--(((A2:A9="East")+(C2:C9="Widget"))>0))

Returns 6. The tempting shortcut =SUMPRODUCT((A2:A9="East")+(C2:C9="Widget")) returns 8, because rows 2 and 7 are both East and Widget and get counted twice. The >0 turns any row with at least one match into a single 1. The same mistake happens with COUNTIF(...)+COUNTIF(...) across two columns.

Compare two columns row by row

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

Returns 4. COUNTIFS can’t do this because its criteria is a single value, not a per-row comparison.

Conditions on a calculated value

=SUMPRODUCT(--(MONTH(F2:F9)=3))

Returns 3. Watch out: an empty cell is treated as 0, and in Excel MONTH(0) returns 1, so blank rows get counted as January. Add *(F2:F9<>"") if your range has blanks.

Case-sensitive counts

COUNTIF treats “Ann” and “ann” the same. =COUNTIF(B2:B9,"ann") returns 3. For a case-sensitive count:

=SUMPRODUCT(--EXACT(B2:B9,"ann"))

Returns 1.

“Contains” without wildcards

SUMPRODUCT compares with =, so "*dg*" would look for literal asterisks. Use SEARCH (not case-sensitive) or FIND (case-sensitive):

=SUMPRODUCT(--ISNUMBER(SEARCH("dg",C2:C9)))

Returns 7 (every Widget and Gadget).

Count and sum in one go

SUMPRODUCT also sums. East Widget revenue:

=SUMPRODUCT((A2:A9="East")*(C2:C9="Widget")*D2:D9)

Returns 900. Multiplying the conditions converts TRUE/FALSE to numbers, so -- isn’t needed here.

Edge cases that give wrong counts

1. Strings longer than 255 characters. Microsoft states COUNTIF returns incorrect results for strings over 255 characters, and suggests splitting the criteria with &. =SUMPRODUCT(--(A2:A9=H1)) has no such limit.
2. Hidden spaces and non-breaking spaces. “East ” doesn’t equal “East” in any of the three. Microsoft’s COUNTIF best practices warn about leading or trailing spaces and nonprinting characters. If counts are too low, see TRIM not working (CHAR 160).
3. Numbers vs. numbers stored as text. Whether COUNTIF(range,1001) also counts a text “1001” depends on the app. In LibreOffice it counted only the number with a numeric criterion, but both with the text criterion "1001". SUMPRODUCT(--(range=1001)) counted only the real number. To be safe, convert the column first (convert numbers stored as text).
4. Long numeric IDs (16+ digits). IDs such as card or tracking numbers stored as text may be read as numbers by COUNTIF and compared using Excel’s 15-digit precision, so different IDs can look like duplicates. Mac Excel counted the two distinct text IDs as 2; Sheets returned 1 and did not reproduce that collision. If you see it, use =SUMPRODUCT(--(A2:A100=A2)), which compares the text exactly.
5. Closed workbooks. Microsoft documents that COUNTIF returns #VALUE! when it refers to a range in another workbook that is closed. Open the source file, or bring the data into the same workbook.
6. Full-column references in SUMPRODUCT. Microsoft warns that SUMPRODUCT(A:A,B:B) processes all 1,048,576 rows of each column. Use bounded ranges or an Excel Table (Orders[Amount]).
7. Unique counts. =SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9)) returns 4 here because COUNTIF ignores case (“Ann” and “ann” are one rep). A blank cell in the range causes #DIV/0!. In Excel 365, =ROWS(UNIQUE(B2:B9)) is easier to read. It returned 4 on the tested Mac, merging Ann/ann; Sheets UNIQUE returned 5, keeping them separate.

Excel vs. Google Sheets

• COUNTIF, COUNTIFS and SUMPRODUCT use the same syntax. Google’s COUNTIFS page also requires all ranges to be the same size.
• =SUM(COUNTIF(A2:A9,{"East","West"})) may return only the first count in Google Sheets unless you wrap it: =ARRAYFORMULA(SUM(COUNTIF(A2:A9,{"East","West"}))).
• SUMPRODUCT handles arrays natively in both, so the SUMPRODUCT formulas need no ARRAYFORMULA.
• Some Sheets locales use \ instead of , to separate columns in array constants.

Common mistakes

1. Omitting explicit Boolean coercion in Excel SUMPRODUCT. Use -- or multiplication; Sheets counted the comparison without -- in the live check, so do not assume identical behavior across apps.
2. Adding COUNTIFs across two columns for OR. Rows matching both get counted twice. Use the >0 SUMPRODUCT pattern.
3. Writing "> H1" instead of ">"&H1. Inside quotes, H1 is just text. Join the operator and the cell with &.
4. Mixing range sizes. COUNTIFS(A2:A9,"East",D2:D10,">500") and SUMPRODUCT(A2:A9,D2:D10) both return #VALUE!.
5. Assuming an empty filter cell means “everything”. COUNTIFS treats an empty criteria cell as 0.

FAQ

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one range and one condition. COUNTIFS takes up to 127 range/condition pairs and counts only the rows where all of them are true.

Can COUNTIFS do OR logic?

Not across different columns. For OR within one column, use =SUM(COUNTIFS(A2:A9,{“East”,“West”})). For OR across columns, use SUMPRODUCT with a >0 test so rows aren’t counted twice.

Why does my SUMPRODUCT return 0?

In Excel, an uncoerced TRUE/FALSE array can be treated as non-numeric. Sheets counted the tested comparison directly. For portable intent, add a double minus: =SUMPRODUCT(—(A2:A9=“East”)), or multiply the conditions together.

Is SUMPRODUCT slower than COUNTIFS?

It does more work per cell and can’t skip unused rows, and Microsoft advises against full-column references in SUMPRODUCT. Use COUNTIFS when it can express the condition, and keep SUMPRODUCT ranges bounded.

Is COUNTIF case-sensitive?

No. “ann” and “Ann” count as the same. Use =SUMPRODUCT(—EXACT(B2:B9,“ann”)) for a case-sensitive count.

Related tutorials

• XLOOKUP returns #N/A even though the value exists
• VLOOKUP returning the wrong value
• FILTER vs QUERY in Google Sheets
• TRIM not working in Excel? Remove non-breaking spaces

Live-test findings

On the tested Mac, MONTH of an empty cell returned 1; Sheets returned 12. Always exclude empty cells explicitly. Sheets SUMPRODUCT of the tested Boolean comparison returned 4 even without --. Sheets COUNTIF with numeric or text criterion 1001 counted both representations (2). For OR arrays, Sheets returned 4 without ARRAYFORMULA and 6 with it; this COUNTIF behavior must not be generalized to SUMIFS.

Sources

• Microsoft Support: COUNTIF function (wildcards and tilde, not case-sensitive, 255-character limit, closed-workbook #VALUE!, spaces and nonprinting characters): https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
• Microsoft Support: COUNTIFS function (127 pairs, same-size ranges, empty criteria cell treated as 0, wildcards): https://support.microsoft.com/en-us/excel/functions/countifs-function
• Microsoft Support: SUMPRODUCT function (non-numeric entries treated as zero, same dimensions or #VALUE!, avoid full-column references, multiplying conditions): https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
• Google Docs Editors Help: COUNTIFS (Sheets syntax, ranges must be the same size): https://support.google.com/docs/answer/3256550
• Google Docs Editors Help: COUNTIF (Sheets syntax and wildcards): https://support.google.com/docs/answer/3093480
• LibreOffice Calc 25.2: used to recalculate every numeric result in this article (local verification).