count clients by code

[Stotty]

Hi everyone,

I am trying to count the number of clients that have a certain code.  I see how to count the number of codes that is in the "# of ees" column. but in the column to the left, it should say "3".  Because there are 3 (unique)clients that

[#2]

@Stotty 

If you share workbook with info as on screenshot (not to retype from the screen to Excel) it will be much easier to suggest you correct formula.

[#3]

@Sergei Baklan 

Do I just add here as a reply?

[#4 · Accepted solution]

I believe this is what you're looking to do:
=COUNTA(UNIQUE(FILTER($I$9:$I$32,$J$9:$J$32=N8)))

PowerPivot can also make short work of this with Distinct Count.

[#5]

@Stotty 

It very depends on which edition of Excel you are. If on 365 when better formulas as above. If not, when

=SUMPRODUCT( ($J$9:$J$32=$N8)/COUNTIFS($I$9:$I$32,$I$9:$I$32, $J$9:$J$32,$J$9:$J$32))

could work. Please check in attached file.