Google Sheets：如何用COUNTIFS统计符合支持邮编的客户数

Hey there! Let's break down how to fix that single-cell limitation in your current formula, plus make it more reliable for your use case.

First, let's call out the issues with your existing formula:

• It only works for one specific cell (right now All Customers!$E$2)—you can't drag it down to check all customers
• If a zip code isn't in your SupportedAddresses list, the MATCH function returns #N/A, which breaks the whole formula

Option 1: Get the Total Count of Eligible Customers in One Go #

If you just need the total number of customers with zip codes in your supported list, this one formula will do it all—no need to check each row individually:

=SUMPRODUCT(--ISNUMBER(MATCH('All Customers'!$E$2:$E, SupportedAddresses!$D$2:$D, 0)))

Here's what it does step-by-step:

1. MATCH checks every zip code in All Customers!E:E against your supported list in SupportedAddresses!D:D
2. ISNUMBER converts valid matches (which return a row number) to TRUE, and non-matches (#N/A) to FALSE
3. The double hyphen (--) turns those TRUE/FALSE values into 1s and 0s
4. SUMPRODUCT adds up all the 1s to give you the total number of eligible customers

Option 2: Flag Individual Customers (For Row-by-Row Checks) #

If you want to mark each customer row as eligible or not (so you can see which ones qualify), use this formula in a new column next to your customer list (e.g., column F) and drag it down:

=IF(ISNUMBER(MATCH('All Customers'!$E2, SupportedAddresses!$D$2:$D, 0)), 1, 0)

This will return 1 for customers with supported zip codes and 0 for those without. You can then use SUM(F:F) to get the total count if needed.

Key Differences From Your Original Formula #

Your original formula uses INDIRECT and COUNTIF to cross-reference the matched zip code cell, which is a roundabout way to do a simple "does this value exist?" check. Here's how our solutions are better:

• No more single-cell restriction: Both options work for entire ranges, no manual cell reference edits needed
• Error-resistant: We use ISNUMBER to handle non-matching zip codes without throwing #N/A errors
• Simpler logic: We're directly checking for existence instead of navigating to a specific cell to compare values

内容的提问来源于stack exchange，提问作者Kiley