Counting unique values with multiple criteria

Tubbies001

New Member
Joined
May 29, 2018
Messages
3
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
 

Some videos you may like

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
Joined
Aug 18, 2015
Messages
10,211
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)))}

<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>
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,700
Members
409,532
Latest member
Lmfacc

This Week's Hot Topics

Top