Count Unique with on multiple If

TJ flyer

New Member
Joined
May 1, 2014
Messages
15
I need a unique count of users within each week.
Sample Data:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Name[/TD]
[TD]Week Number[/TD]
[TD]Sum of Unique Count of users each week (Need Formula for this one)[/TD]
[TD]Expected RESULT[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD]Donald[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD]Goofy[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD]Minnie[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/2/2017[/TD]
[TD]Donald[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/2/2017[/TD]
[TD]Minnie[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/15/2017[/TD]
[TD]Donald[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2/16/2017[/TD]
[TD]Donald[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2/16/2017[/TD]
[TD]Minnie[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Any ideas on this one?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Why would you get a 3 For Donald in Week-5? Same for Goofy and Minnie?


I would expect Donald to be a 2 for Week-2, Goofy to be a 1 and Minnie to be a 1.
 
Upvote 0
I am trying to get a unique count of users each week. In Week number 5 Donald, Goofy, and Minnie have entries. So the count is 3. However, in Week 7 only two people have entries.
 
Upvote 0
Assuming there will be no blank cells in the name column try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.
Excel Workbook
ABCD
1DateNameWeek NumberSum of Unique Count of users each week (Need Formula for this one)
22/1/2017Donald53
32/1/2017Goofy53
42/1/2017Minnie53
52/2/2017Donald53
62/2/2017Minnie53
72/15/2017Donald72
82/16/2017Donald72
92/16/2017Minnie72
Sheet
 
Upvote 0
I was able to get the right result by adding four new columns and using each column to do part of what I needed:
In Column F I created a unique combination of User, Year and Week Number with this formula: =B2&"-"&YEAR(A2)&"-"&WEEKNUM(A2)
In Column G I created a Unique Week and Year number to use in the SumIF formula: =YEAR(A2)&"-"&WEEKNUM(A2)
In Column H I created a count formula that is fixed such that it will count the first time it shows up: =IF(COUNTIF($F$1:F2,F2)>1,"",1)
Finally, in Colunm I I created a SumIF formula that uses Column G to and then sum the values in Column H: =SUMIF($G$2:$G$9,G2,$H$2:$H$9)

I am still hoping to find one formula that would do all of this. But this at least gets me the right result.
 
Upvote 0
I used the same formula that is in post #4 . My actual sheet has over 50,000 rows of data and my computer freezes up when I inserted that formula and expanded it to include all of the data. For some reason my own solution of adding four columns to get to the same result does not have the same affect on the performance.
 
Upvote 0
I used the same formula that is in post #4 . My actual sheet has over 50,000 rows of data and my computer freezes up when I inserted that formula and expanded it to include all of the data. For some reason my own solution of adding four columns to get to the same result does not have the same affect on the performance.
Is this admissible qua performance?

In D2 control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ISNUMBER(MATCH(C2,$C$1:C1,0)),VLOOKUP($C2,$C$1:D1,2,0),SUM(IF(FREQUENCY(IF($B$2:$B$9<>"",
     IF($C$2:$C$9=$C2,MATCH($B$2:$B$9,$B$2:$B$9,0))),ROW($B$2:$B$9)-ROW($B$2)+1),1)))
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top