?
Solved

Users and unique locations

Posted on 2016-08-24
14
Medium Priority
?
43 Views
Last Modified: 2016-08-26
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
Comment
Question by:Gordon Smith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 26

Expert Comment

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

Accepted Solution

by:
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

by:Gordon Smith
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Gordon Smith
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 26

Expert Comment

by:ProfessorJimJam
ID: 41769517
please find attached.
Book1.xlsx
0
 

Author Comment

by:Gordon Smith
ID: 41769538
Can't download it Professor. Our company doesn't allow it. Thanks for the effort.
0
 
LVL 26

Expert Comment

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

2016-08-25-00_57_49-Microsoft-Excel-.png
0
 
LVL 32

Expert Comment

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

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

Open in new window

0
 

Author Comment

by:Gordon Smith
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 26

Expert Comment

by:ProfessorJimJam
ID: 41770389
smidy

sent file
0
 

Author Comment

by:Gordon Smith
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 32

Expert Comment

by:Subodh Tiwari (Neeraj)
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

by:Gordon Smith
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 26

Expert Comment

by:ProfessorJimJam
ID: 41772404
You are welcome.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question