# Counting unique values with multiple criteria

#### Tubbies001

##### New Member
Dear Forum Members,

I would like to build a formula that would count Consultant IDs (only once, as they repeat) of consultants with Start Month "04" and the kit "S" (so in my below example the result is 1).
Consultant IDStart monthKit
 100731

<tbody>
</tbody>
03S
10073204F
10073204F
10073204F
10073502J
10073502J
10073704S
10073704S

<tbody>
</tbody>

Thank you very much for any tips!
Margo

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Eric W

##### MrExcel MVP
Welcome to the Board.

If the IDs are numeric, try the E2 formula, otherwise the E3 formula:

ABCDE
1Consultant IDStart monthKit
21007313S1
31007324F1
41007324F
51007324F
61007352J
71007352J
81007374S
91007374S

</tbody>
Sheet2

Array Formulas
CellFormula
E2{=SUM(SIGN(FREQUENCY(IF(B2:B9=4,IF(C2:C9="S",A2:A9)),A2:A9)))}
E3{=SUM(SIGN(FREQUENCY(IF(B2:B9=4,IF(C2:C9="S",MATCH(A2:A9,A2:A9,0))),ROW(A2:A9)-ROW(A2)+1)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Replies
6
Views
97
Replies
12
Views
483
Replies
45
Views
4K
Replies
6
Views
46
Replies
6
Views
296