Thanks:  0
Likes:  0

# Thread: Count Unique with on multiple If

1. ## Count Unique with on multiple If

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

Any ideas on this one?

2. ## Re: Count Unique with on multiple If

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.

3. ## Re: Count Unique with on multiple If

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.

4. ## Re: Count Unique with on multiple If

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.

 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

 Cell Formula D2 {=SUM(IF(FREQUENCY(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))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Count Unique with on multiple If

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.

6. ## Re: Count Unique with on multiple If

Did you try the formula in post#4?

7. ## Re: Count Unique with on multiple If

Yes, but now my spreadsheet crashes.

8. ## Re: Count Unique with on multiple If

Originally Posted by TJ flyer
Yes, but now my spreadsheet crashes.
Care to post the formula as you implemented it?

9. ## Re: Count Unique with on multiple If

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.

10. ## Re: Count Unique with on multiple If

Originally Posted by 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.
Is this admissible qua performance?

In D2 control+shift+enter, not just enter, and copy down:
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)))
```

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•