count distinct values and return required group categories

[redJohn89]

Hi,

I want to write a formula which will give me a sum of the count of distinct values in Column B (User). Based on the value from Col B i want the desired output in Column C (Output). If Distinct sum is >3, then output = "Large". If distinct sum = 3, then output = "Medium" and if Distinct sum < 3, then output = "Small". In my example, the distinct count of users for Boston = 3, for Chicago = 2, LA=2 and Denver = 5. Hope you can assist me with the formula

[FDibbins]

Not sure how you get your answers (you have a count of 1 as Large and a count of 4 as Small?), but try either of these...

=INDEX({"small","medium","large"},MIN(3,COUNTIF($B$2:$B$18,B2)))
or
=IF(COUNTIF($B$2:$B$18,B2)<3,"small",IF(COUNTIF($B$2:$B$18,B2)=3,"medium","large"))
copied down

[redJohn89]

Hi FDibbins,

Thanks for getting back to me. Let me explain my logic again as your solution is not getting me the desired end result. So Boston has Users listed as A1,A1,A2 and A3. So the distinct count of users is only 3 which is A1,A2,A3.
Similarly for Denver, there are 5 users, D1,D2,D3, D4,D5. So the distinct count of Denver users is 5 (which is D1-D5). Now using this count of distinct users we come up with the categories of small , medium and large.
Hope this clarified things, if not please let me know.

[AliGW]

Are you still using Excel 2010? If not, please update your profile. Thanks.

[redJohn89]

Hi AliGW,

Thanks for bringing this to my attention. I have updated my excel version now to 2016. My apologies.

[JohnTopley]

In D2

=IF(COUNTIF($A$2:$A2,$A2)=1,SUMPRODUCT(1/COUNTIF(OFFSET($A2,,1,COUNTIF($A:$A,$A2)),OFFSET($A2,,1,COUNTIF($A:$A,$A2)))),"")

in E2

=IF(COUNTIF($A$2:$A2,$A2)=1,IF($D2<3, "Small",IF($D2=3,"Medium","Large")),"")

[redJohn89]

Hello John,

You are almost there. How do we get the values to populate under the blank cells? Like Cell D3, D4, D5 should have the same value as cell D2 which is 3 instead of blank cells. Similarly for other cells below. Would that be possible to incorporate?

[JohnTopley]

=IF(COUNTIF($A$2:$A2,$A2)=1,SUMPRODUCT(1/COUNTIF(OFFSET($A2,,1,COUNTIF(A:A,A2)),OFFSET($A2,,1,COUNTIF(A:A,A2)))),D1)

=IF(D2<3,"Small",IF(D2=3,"Medium","Large"))

[redJohn89]

wow...that worked like a charm John. I had to move around the columns a little bit and the formula stopped working. Could you tell me what minor adjustment i need to make in the formula of the newly attached file? Col P and Col Q is where i listed your formulas which will need a slight tweaking.

[JohnTopley]

try

=IF(COUNTIF($A$2:$A2,$A2)=1,SUMPRODUCT(1/COUNTIF(OFFSET($A2,,8,COUNTIF(A:A,A2)),OFFSET($A2,,8,COUNTIF(A:A,A2)))),P1)

The BOLD number is the column offset from column A where the Use is (now in I) which 8 columns along from A

[redJohn89]

Thank you so very much John. The explanations really helped and the solution will save me a ton of time. I really appreciate your help...God bless !

[JohnTopley]

You're welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.