Count Unique with on multiple If

[TJ flyer]

I need a unique count of users within each week.
Sample Data:

Date	Name	Week Number	Sum of Unique Count of users each week (Need Formula for this one)	Expected RESULT
2/1/2017	Donald	5		3
2/1/2017	Goofy	5		3
2/1/2017	Minnie	5		3
2/2/2017	Donald	5		3
2/2/2017	Minnie	5		3
2/15/2017	Donald	7		2
2/16/2017	Donald	7		2
2/16/2017	Minnie	7		2

<tbody>
</tbody>

Any ideas on this one?

 

[philwojo]

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.

 

[TJ flyer]

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.

 

[AhoyNC]

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
	A	B	C	D
1	Date	Name	Week Number	Sum of Unique Count of users each week (Need Formula for this one)
2	2/1/2017	Donald	5	3
3	2/1/2017	Goofy	5	3
4	2/1/2017	Minnie	5	3
5	2/2/2017	Donald	5	3
6	2/2/2017	Minnie	5	3
7	2/15/2017	Donald	7	2
8	2/16/2017	Donald	7	2
9	2/16/2017	Minnie	7	2
Sheet

 

[TJ flyer]

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.

 

[AhoyNC]

Did you try the formula in post#4?

 

[TJ flyer]

Yes, but now my spreadsheet crashes.

 

[Aladin Akyurek]

Yes, but now my spreadsheet crashes.

Care to post the formula as you implemented it?

 

[TJ flyer]

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.

 

[Aladin Akyurek]

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:

=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)))