• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

COUNT or SUM unique values in a range

KevinFlame

KevinFlame

New Member
Hi folks

Had some great help the last time I posted a question, so hopefully someone can help me with this too...

I have a table (called compRoles) that contains the columns: Role; User Name; and Indirect User-Role Assignment.

Problem 1
Cells F5:F9 contain a list of role names. I want to count the number of unique users in the table that do not have any of these roles.

I have got so far using the following array formula, but this only counts the number of unique users without the role in cell F5:

{=SUM(IF(FREQUENCY(IF(compRoles[Role]<>Calcs!$F$5,MATCH(compRoles[User Name],compRoles[User Name],0)),IF(compRoles[Role]<>Calcs!$F$5,MATCH(compRoles[User Name],compRoles[User Name],0)))>0,1))}

If I change the <> part of the formula to look at Calcs!$F$5:$F$9, I get the #N/A error. Can someone explain where I am going wrong please?

Problem 2
Cells E5:E6 contain the two permissible values that appear in the Indirect User-Role Assignment column of my table. I want to count the number of unique users that have the same role more than once, but is assigned using both of the values in E5 and E6. I'm afraid I have no idea where to start on this one, so any help greatly appreciated!

--
James
 
Use below formula in B5 of sheet Calcs.
=SUM(IF(ISNA(MATCH(compRoles[Role],Calcs!F5:F9,0)),1,0))

Just see if it giving correct result. Mean time i will work on your other problem.

Regards,
 
Hi Somendra

Thanks for replying, I've tried that formula and it has given me the number of rows that do not match the five values in F5 to F9. But I need to also check which users do not have any of these, so I guess I need to put your formula in to my array formula somehow. I'm not sure how to do this, but I'll try a few things!

Thanks
 
Hi Kevin,

I get all except the last row that does not have
ZUXXXPHRATSE

This was obtained using Advanced Filter, the criteria is on the row 13&14 of the calc sheet.

If you are not sure how to use the function please let me know. I will try to explain
 

Attachments

  • Chandoo sample.xlsx
    12.7 KB · Views: 5
Last edited:
Thanks for trying, but I need to check who doesn't have any of the five roles - not just ZUXXXPHRATSE.

I also need to do it in a formula, as I need to do various checks on the raw data, then present the numbers on another sheet.

Sorry to be a pain!
 
Hi Somendra

As per the sample workbook:

Problem 1
Peter Pan has the role ZUXXXPHRATSE, and Micky Mouse has ZUXXXPHRSSPY. The other two users do not have any of the five roles listed on the Calcs sheet - so the answer to this question would be 2.

Maybe I should add another column to the table, and insert an IF statement to see if the user has a role on the Calcs sheet. Then I could just count the number of FALSE values?

Problem 2
Only Micky Mouse has the same role twice, and the assignment is both Direct and from HR OM. So the answer to this would be 1.
 
Other way around please. Number of unique users whose role does not match with any of the roles in the calc sheet.
 
It doesn't give me the answer I was expecting sorry, Somendra. Plus this only seems to be looking at the Roles data, and doesn't look at the user names?
 
I'm getting somewhere now... Have attached a new version of the spreadsheet for info.

I added a new column to the table to check if the role in that row was on the calcs sheet. If it is, I insert a 1, if not, a zero.

I then added another new column, and put this array formula in it:

=MAX(IF($C$2:$C$26=$C2,IF($F$2:$F$26=1,1,$F$2:$F$26)))

This checks if the user has any row where the check equals 1, and if it does, it makes all the rows show as 1.

Then in my calcs sheet, I put this array formula:

=SUM(IF(FREQUENCY(IF(compRoles[Has a golden role]=0,MATCH(compRoles[User Name],compRoles[User Name],0)),IF(compRoles[Has a golden role]=0,MATCH(compRoles[User Name],compRoles[User Name],0)))>0,1))

It's a bit long winded, but it seems to work. Is there a simpler way of doing this though?
 

Attachments

  • Chandoo sample v2.xlsx
    15.7 KB · Views: 7
Try using in B5

=SUM(IFERROR(IF(ISNA(MATCH(compRoles[Role],Calcs!F5:F9,0)),IF(FREQUENCY(IFERROR(MATCH(compRoles[Full Name],compRoles[Full Name],0),0),ROW(compRoles[Full Name])-ROW(Table!$D$2)+1),1,0)),0))-SUM(IFERROR(IF((MATCH(compRoles[Role],Calcs!F5:F9,0)),IF(FREQUENCY(IFERROR(MATCH(compRoles[Full Name],compRoles[Full Name],0),0),ROW(compRoles[Full Name])-ROW(Table!$D$2)+1),1,0)),0))
 
No sorry, that equals 4 but the value should be 2.

I think I'll just have to use the helper columns, but thanks for all your efforts - this was a tough one!
 
Yep, I have entered it using Ctrl+Shift+Enter but it still says 4 (double-checked that the {} brackets were present too). Would attach a screenshot, but the work proxy blocks most file hosting sites...
 
Final try of the day....
=SUM(IFERROR(IF(ISNA(MATCH(compRoles[Role],Calcs!F5:F9,0)),IF(FREQUENCY(IFERROR(MATCH(compRoles[Full Name],compRoles[Full Name],0),0),ROW(compRoles[Full Name])-ROW(Table!$D$2)+1),1,0)),0))-SUM(IFERROR(IF((MATCH(compRoles[Role],Calcs!F5:F9,0)),1,0),0))
 
Sorry, it doesn't work either Somendra.

It gives the right number in the sample, but if you add more entries to the table it gives you the wrong answer...

If you copy and paste the records so the table goes to row 51, the formula returns 0. If you copy and paste the records again, it returns -2.

Appreciate all your help, but I think what I'm trying to do is just too complicated! I may have to text Stephen Hawking and see if he knows anything about Excel... :)
 
You must log in or register to reply here.
Back
Top