SOLVED

count clients by code

%3CLINGO-SUB%20id%3D%22lingo-sub-2332013%22%20slang%3D%22en-US%22%3Ecount%20clients%20by%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2332013%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20count%20the%20number%20of%20clients%20that%20have%20a%20certain%20code.%26nbsp%3B%20I%20see%20how%20to%20count%20the%20number%20of%20codes%20that%20is%20in%20the%20%22%23%20of%20ees%22%20column.%20but%20in%20the%20column%20to%20the%20left%2C%20it%20should%20say%20%223%22.%26nbsp%3B%20Because%20there%20are%203%20(unique)clients%20that%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screen%20Shot%202021-05-06%20at%2012.29.26%20PM.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F278819i3A9644D2E9CE78AE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-05-06%20at%2012.29.26%20PM.png%22%20alt%3D%22Screen%20Shot%202021-05-06%20at%2012.29.26%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2332013%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2332240%22%20slang%3D%22en-US%22%3ERe%3A%20count%20clients%20by%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2332240%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047088%22%20target%3D%22_blank%22%3E%40Stotty%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20share%20workbook%20with%20info%20as%20on%20screenshot%20(not%20to%20retype%20from%20the%20screen%20to%20Excel)%20it%20will%20be%20much%20easier%20to%20suggest%20you%20correct%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2332365%22%20slang%3D%22en-US%22%3ERe%3A%20count%20clients%20by%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2332365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20I%20just%20add%20here%20as%20a%20reply%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

 

Screen Shot 2021-05-06 at 12.29.26 PM.png

4 Replies

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

@Sergei Baklan 

 

Do I just add here as a reply?

best response confirmed by Stotty (New Contributor)
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.

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