SOLVED

# count clients by code

Copper Contributor

# count clients by code

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

4 Replies

# Re: count clients by code

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.

# Re: count clients by code

best response confirmed by Stotty (Copper Contributor)
Solution

# Re: count clients by code

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.

# Re: count clients by code

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.

1 best response

Accepted Solutions
best response confirmed by Stotty (Copper Contributor)
Solution

# Re: count clients by code

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.