Counting unique values with multiple criteria

[Tubbies001]

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 ID	Start month	Kit
100731 <tbody> </tbody>	03	S
100732	04	F
100732	04	F
100732	04	F
100735	02	J
100735	02	J
100737	04	S
100737	04	S

<tbody>
</tbody>

Thank you very much for any tips!
Margo

 

[Eric W]

Welcome to the Board.

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

	A	B	C	D	E
1	Consultant ID	Start month	Kit
2	100731	3	S		1
3	100732	4	F		1
4	100732	4	F
5	100732	4	F
6	100735	2	J
7	100735	2	J
8	100737	4	S
9	100737	4	S

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula 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)))} <thead> </thead><tbody> </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>