Excel条件计数需求：跨区域匹配且右侧为TRUE，及指定人员符合条件的工作统计

Alright, let's break down your two Excel counting problems step by step—here's how to tackle each one:

1. Count cells that match a range AND have TRUE in the adjacent right cell #

Suppose your target cells (the ones you want to check) are in range A2:A10, the range you need to match against is D2:D5, and the adjacent right cells (where you check for TRUE) are B2:B10.

The most flexible formula for this (works in all Excel versions, no array formula tricks needed) is using SUMPRODUCT:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A10, D2:D5, 0))), --(B2:B10=TRUE))

Let's break this down:

• ISNUMBER(MATCH(A2:A10, D2:D5, 0)): Checks if each value in A2:A10 exists in the match range D2:D5 (returns TRUE/FALSE)
• --(...): Converts those TRUE/FALSE values to 1s and 0s (since SUMPRODUCT works with numeric values)
• The second --(B2:B10=TRUE) does the same conversion for the adjacent right cell condition
• SUMPRODUCT multiplies the corresponding 1s/0s from each condition and sums the total—giving you the count of cells that meet both requirements.

2. Count completed tasks for specific people that are in a target task list #

Let's assume:

• Table1 has columns Person (column A) and Task (column B)
• Table2 has a column Target Tasks (column D) with values F and G

For John (expected result: 2) #

Use this SUMPRODUCT formula (compatible with all Excel versions):

=SUMPRODUCT(--(Table1[Person]="John"), --(ISNUMBER(MATCH(Table1[Task], Table2[Target Tasks], 0))))

Or if you're using Excel 365/2021 (which supports dynamic arrays), you can use a simpler SUM + COUNTIFS combo:

=SUM(COUNTIFS(Table1[Person],"John",Table1[Task],Table2[Target Tasks]))

For Peter (expected result: 2) #

Just swap "John" for "Peter" in either formula:

=SUMPRODUCT(--(Table1[Person]="Peter"), --(ISNUMBER(MATCH(Table1[Task], Table2[Target Tasks], 0))))

How this works:

• The first condition filters rows where the person is John/Peter
• The second condition checks if the task is in the target list from Table2
• The formula sums up all rows that meet both conditions—exactly matching your expected results (John has 2 F tasks, Peter has 1 F and 1 G task, all in the target list).

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