Workbook2.xlsx
See attached sample. What I'd like to do is count how many times 'Bob' is listed with unique dates. So in the sample, Bob would be '4', as he's listed again May 20, 21, 22, and 23,and 'John' would be '3' as his name is listed against May 20,21,22.
If I could get something that would create a list of all unique entries in column 2, with the 'count' result next to that?
Hopefully this is explained ok?!
Thanks for your help.
Try this...
Data Range
This array formula** entered in E1 and copied down:
=SUM(IF(FREQUENCY(IF(B$1:B$91=D1,A$1:A$91),A$1:A$91),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
c1=d1
Please Login or Register to view this content.and drag down.Please Login or Register to view this content.
The values that represent 1 are the unique values.
Eventualy you can filter them.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi,
In C1
=B1&A1
In D1
=IF(MAX(1,COUNTIF($C$1:$C1,$C1))>1,0,1)
both copied down. Then apply a pivot table to A1:Dnn and put column D in the PT along with the names and dates.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Or try this Regular Formula (not array formula)
In E1
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
@ Tony .How does the frequency part work here? Just trying to understand. Thanks
Avi
FREQUENCY returns a series of "count if" results.
In this case it's a conditional count if based on the corresponding name.
Consider this example:
Data Range
We want to count the unique dates that correspond to Tom.
The dates that correspond to Tom are:
5/20/2014
5/20/2014
5/21/2014
FREQUENCY performs a seires of "count ifs" on the dates that fall into the date bins:
A1: 5/20/2014
A2: 5/20/2014
A3: 5/20/2014
A4: 5/21/2014
A5: 5/21/2014
Only the unique bins generate a count.
The "count ifs" would be:
Count If 5/20/2014 is less than or equal to 5/20/2014 = 2
Count If 5/20/2014 = already counted = 0
Count If 5/20/2014 = already counted = 0
Count If 5/21/2014 is greater than 5/20/2014 and less than or equal to 5/21/2014 = 1
Count If 5/21/2014 = already counted = 0
Count If any dates are greater than 5/21/2014 = 0
So the FREQUENCY function returns this array:
{2;0;0;1;0;0}
From that array we want to count how many elements are greater than 0.
So, the count of unique dates that correspond to Tom = 2.
I'm pretty sure this is confusing but if you read it a few times it might make sense!
Thank you so much. I Almost broke down your formula and understood through evaluate formula and got about 80% of it. Now its more clear. Thanks for detailed explanation.
You're welcome. Thanks for the feedback!
Sorry Just one more Question. In your formula frequency has something like
Frequency({false,false,41780},$A1:A$91) and i believe frequency evaluates to zero there for false? Am i correct
The Boolean FALSE is ignored.
Thanks a ton for patience.
You're welcome!
Amazing... Thanks for all the replies! I ended up using Richard's method, which was fairly fast.
In C1
=B1&A1
In D1
=IF(MAX(1,COUNTIF($C$1:$C1,$C1))>1,0,1)
both copied down. Then apply a pivot table to A1:Dnn and put column D in the PT along with the names and dates.
All seemed to do the trick in one form or another.
Sktneer - Thanks for this one - Probably would have been the easiest solution.
=SUMPRODUCT(--(FREQUENCY(INDEX((B$1:B$91=D1)*A$1:A$91,0),A$1:A$91)>0))
You're welcome. Thanks for the feedback and rep.
The SUM(IF array version is more efficient (faster to calculate).Originally Posted by hambly
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks