I apologize as I'm sure this has been answered verbatim in another post but after searching I could only find pieces and wasn't able to put it together myself. I found a Formula that counts all unique items in column E, but I need that to based on location and date. Here is a sample of my data and this is the formula I'm using that is giving me a count of all unique values. Hope that makes sense. Thanks in advance for the help.
=SUMPRODUCT(--(FREQUENCY(MATCH($D$2:$D$265,$D$2:$D$265,0),ROW($D$2:$D$265)-ROW($D$2)+1)>0))
<tbody>
</tbody>
<tbody>
</tbody>
=SUMPRODUCT(--(FREQUENCY(MATCH($D$2:$D$265,$D$2:$D$265,0),ROW($D$2:$D$265)-ROW($D$2)+1)>0))
C | D | E | F | |
---|---|---|---|---|
1 | Report Date | Location | Prop | Unique Value Per Location per Date |
2 | 5/31/2018 | Home | ABC | 3 |
3 | 5/31/2018 | Home | ABC | 3 |
4 | 5/31/2018 | Home | ABCC | 3 |
5 | 5/31/2018 | Home | ABCC | 3 |
6 | 5/31/2018 | Home | ABCCC | 3 |
7 | 5/31/2018 | Home | ABCCC | 3 |
8 | 5/31/2018 | Away | BCD | 6 |
9 | 5/31/2018 | Away | BCD | 6 |
10 | 5/31/2018 | Away | BCD | 6 |
11 | 5/31/2018 | Away | BCDD | 6 |
12 | 5/31/2018 | Away | BCDDD | 6 |
13 | 5/31/2018 | Away | BCDDDD | 6 |
14 | 5/31/2018 | Away | BCDDDDD | 6 |
15 | 5/31/2018 | Away | BCDDDDDD | 6 |
16 | 6/1/2018 | Home | ABC | 3 |
17 | 6/1/2018 | Home | ABC | 3 |
18 | 6/1/2018 | Home | ABCC | 3 |
19 | 6/1/2018 | Home | ABCC | 3 |
20 | 6/1/2018 | Home | ABCCC | 3 |
21 | 6/1/2018 | Home | ABCCC | 3 |
22 | 6/1/2018 | Away | BCD | 7 |
23 | 6/1/2018 | Away | BCD | 7 |
24 | 6/1/2018 | Away | BCDD | 7 |
25 | 6/1/2018 | Away | BCDDD | 7 |
26 | 6/1/2018 | Away | BCDDDD | 7 |
27 | 6/1/2018 | Away | BCDDDDD | 7 |
28 | 6/1/2018 | Away | BCDDDDDD | 7 |
29 | 6/1/2018 | Away | BCDDDDDDD | 7 |
<tbody>
</tbody>
Sheet5
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>