多列匹配值计数需求：求适配3-4列的ARRAYFORMULA公式

Approach #

To count values that appear in all specified columns, follow this logic:

1. Extract all unique values across the target columns.
2. Verify if each unique value exists in every target column.
3. Count the number of values that pass this check.

Formula for 3 Columns (A, B, C) #

Use this formula to count values present in all three columns:

=COUNTA(BYROW(UNIQUE({A2:A4; B2:B4; C2:C4}), LAMBDA(x, IF(AND(COUNTIF(A2:A4,x)>0, COUNTIF(B2:B4,x)>0, COUNTIF(C2:C4,x)>0), x, ""))))

Breakdown:

• UNIQUE({A2:A4; B2:B4; C2:C4}) gets all distinct values from columns A, B, C.
• BYROW loops through each unique value.
• LAMBDA(x, ...) checks if value x exists in each column (using COUNTIF>0 to confirm presence).
• COUNTA counts non-blank results (values that exist in all three columns).

For your example data, this returns 2 (values 999 and 100), matching your expected result.

Formula for 4 Columns (A, B, C, D) #

Adjust the formula to include column D:

=COUNTA(BYROW(UNIQUE({A2:A4; B2:B4; C2:C4; D2:D4}), LAMBDA(x, IF(AND(COUNTIF(A2:A4,x)>0, COUNTIF(B2:B4,x)>0, COUNTIF(C2:C4,x)>0, COUNTIF(D2:D4,x)>0), x, ""))))

This returns 1 (only value 999 exists in all four columns), as expected.

Customization Tips #

• To add more columns: Extend the UNIQUE array (e.g., {A2:A4; B2:B4; C2:C4; D2:D4; E2:E4}) and add an extra COUNTIF check inside the AND function.
• To use entire columns: Replace range references like A2:A4 with A:A, but add FILTER(A:A, A:A<>"") inside the UNIQUE function to exclude blank cells if needed.

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