如何组合两个COUNTUNIQUEIFS函数——实现复合条件下的唯一客户统计

Let's break down how to solve both of your unique customer counting needs using your provided dataset:

1. Count unique customers with at least 1 active license AND at least 1 connected account #

The key here is to first flag customers who have any "Yes" in the Active License column and any "Yes" in the Account Connected column, then count their unique names. Use this formula:

=COUNTUNIQUE(FILTER(A2:A, (COUNTIFS(A:A, A2:A, B:B, "Yes") > 0) * (COUNTIFS(A:A, A2:A, C:C, "Yes") > 0)))

How it works: #

• COUNTIFS(A:A, A2:A, B:B, "Yes") > 0: Checks if a customer has at least one active license marked "Yes"
• COUNTIFS(A:A, A2:A, C:C, "Yes") > 0: Checks if the same customer has at least one connected account marked "Yes"
• The * acts as a logical AND—only customers meeting both conditions are retained
• FILTER pulls all matching customer names, then COUNTUNIQUE removes duplicates and counts the unique entries

For your dataset, this returns 1 (only Alex meets both criteria).

2. Count unique customers with at least 1 active license but NO connected accounts #

This uses similar logic, but we adjust the second condition to ensure the customer has no "Yes" entries in the Account Connected column. Use this formula:

=COUNTUNIQUE(FILTER(A2:A, (COUNTIFS(A:A, A2:A, B:B, "Yes") > 0) * (COUNTIFS(A:A, A2:A, C:C, "Yes") = 0)))

How it works: #

• The first condition (COUNTIFS(...) > 0) identifies customers with at least one active license
• COUNTIFS(A:A, A2:A, C:C, "Yes") = 0: Confirms the customer has no connected accounts (no "Yes" entries in that column)
• The * combines the two conditions, FILTER grabs the matching names, and COUNTUNIQUE counts the unique ones

For your dataset, this returns 1 (only Barry fits this profile).

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