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
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
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
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.
Are you still using Excel 2010? If not, please update your profile. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi AliGW,
Thanks for bringing this to my attention. I have updated my excel version now to 2016. My apologies.
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")),"")
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?
=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"))
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.
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
Last edited by JohnTopley; 09-09-2021 at 10:28 AM.
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 !
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks