Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Users and unique locations

Posted on 2016-08-24
Medium Priority
46 Views
Hello Experts!

I am trying to come up with a formula that will allow me to take a user’s name in one column and count all of the unique cells in another column.

The user’s names are in column P and the information I want to count is in column C. They may have looked at the same location several times in a month but I only want to know how many in the entire month are unique.

Names Ranges:
User Name = User
Location = Locs

Example:
User Name       Location
Counter A             121
122
123
121

Counter B             121
122
122
121
Counter A counted 3 unique locations out of the 4 that were counted.
Counter B counted 2 unique locations out of the 4 that were counted.

I've tried a couple (both as an array and just a formula) with the same results, "0":

=SUM(IF(B13=User,1/(COUNTIFS(B14,Locs)),0))
=IF(B13=User,1/(COUNTIFS(User,B13,Locs,Locs)),0)
0
Question by:Gordon Smith
• 6
• 6
• 2

LVL 27

Expert Comment

ID: 41769477
can you please upload a sample file, as I don't understand the reference of B13
0

LVL 27

Accepted Solution

ProfessorJimJam earned 2000 total points
ID: 41769487
Assuming your User and Locs are the names range and the B13 is the criteria value which is Counter A for users and  B14 criteria value which is Counter B then use this formula

=SUM(IF(B13=User, 1/(COUNTIFS(User, B13, Locs, Locs)), 0))

Enter with control shift enter
And drag down the formula
0

Author Comment

ID: 41769489
Sorry Poressorjimjam,
It references Counter A or B.

I see I entered B14 on the first formula instead of B13.

Hopefully you can start with that. I will need to change a lot of data before I could upload this particular file. I'll something worked up as soon as I can.

Apologies.
0

Author Comment

ID: 41769504
No Go Professorjimjam, =SUM(IF(B13=User, 1/(COUNTIFS(User, B13, Locs, Locs)), 0)).

I think that was one I had tried as well. I didn't show all of the attempts in the original post.

Sorry for the earlier finger farts. Rushing too much.
0

LVL 27

Expert Comment

ID: 41769517
Book1.xlsx
0

Author Comment

ID: 41769538
Can't download it Professor. Our company doesn't allow it. Thanks for the effort.
0

LVL 27

Expert Comment

ID: 41769547
then see the screenshot and replicate it in your workbook.

0

LVL 34

Expert Comment

ID: 41769574
Or try this regular (non-array formula)
Based on Professor's screenshot

``````=SUMPRODUCT(--(FREQUENCY(INDEX((User=B13)*Locs,0),Locs)>0))
``````
0

Author Comment

ID: 41770347
Professorjimjam,
Could you send the screenshot to smidyz55@gmail.com? I can't see it. Company's tight with their internet.
Thanks.
0

LVL 27

Expert Comment

ID: 41770389
smidy

sent file
0

Author Comment

ID: 41771031
Subodh,

I'm not clear on your formula.
=SUMPRODUCT(--(FREQUENCY(INDEX((User=B13)*Locs,0),Locs)>0)). What is the information supposed to be where you have the (--(?
0

LVL 34

Expert Comment

ID: 41771186
In the formula part --(FREQUENCY(INDEX((User=B13)*Locs,0),Locs)>0), FREQUENCY(INDEX((User=B13)*Locs,0),Locs)>0 returns True  and False for all the unique and duplicate values respectively and -- (double negative) converts those True and False into 1 and 0 respectively and then Sumproduct adds all the 1s and 0s.
0

Author Comment

ID: 41772390
Thanks Subodh,
Your formula works fine in the Professor's workbook but it doesn't in mine. I get a #VALUE! error. I copied it exactly from your info to mine. Granted my workbook is quite a bit larger but I wouldn't think that would make a difference.

Professorjimjam,
I got the array formula to work after I changed my named ranges to \$P2:\$P50000 from \$P:\$P. It seems to take a long time to work though the calculations.

So I'll give the points to Professorjimjam.

I thank you both for the help.
0

LVL 27

Expert Comment

ID: 41772404
You are welcome.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month12 days, left to enroll