Count Unique Text Values of Each Location Based on a Report Date

[hullu]

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

	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 Cell Formula C1 Report Date D1 Location E1 Prop F1 Unique Value Per Location per Date C2 43251 D2 Home E2 ABC F2 3 C3 43251 D3 Home E3 ABC F3 3 C4 43251 D4 Home E4 ABCC F4 3 C5 43251 D5 Home E5 ABCC F5 3 C6 43251 D6 Home E6 ABCCC F6 3 C7 43251 D7 Home E7 ABCCC F7 3 C8 43251 D8 Away E8 BCD F8 6 C9 43251 D9 Away E9 BCD F9 6 C10 43251 D10 Away E10 BCD F10 6 C11 43251 D11 Away E11 BCDD F11 6 C12 43251 D12 Away E12 BCDDD F12 6 C13 43251 D13 Away E13 BCDDDD F13 6 C14 43251 D14 Away E14 BCDDDDD F14 6 C15 43251 D15 Away E15 BCDDDDDD F15 6 C16 43252 D16 Home E16 ABC F16 3 C17 43252 D17 Home E17 ABC F17 3 C18 43252 D18 Home E18 ABCC F18 3 C19 43252 D19 Home E19 ABCC F19 3 C20 43252 D20 Home E20 ABCCC F20 3 C21 43252 D21 Home E21 ABCCC F21 3 C22 43252 D22 Away E22 BCD F22 7 C23 43252 D23 Away E23 BCD F23 7 C24 43252 D24 Away E24 BCDD F24 7 C25 43252 D25 Away E25 BCDDD F25 7 C26 43252 D26 Away E26 BCDDDD F26 7 C27 43252 D27 Away E27 BCDDDDD F27 7 C28 43252 D28 Away E28 BCDDDDDD F28 7 C29 43252 D29 Away E29 BCDDDDDDD F29 7 <tbody> </tbody>

<tbody>
</tbody>

 

[Sahitya]

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

	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 Cell Formula C1 Report Date D1 Location E1 Prop F1 Unique Value Per Location per Date C2 43251 D2 Home E2 ABC F2 3 C3 43251 D3 Home E3 ABC F3 3 C4 43251 D4 Home E4 ABCC F4 3 C5 43251 D5 Home E5 ABCC F5 3 C6 43251 D6 Home E6 ABCCC F6 3 C7 43251 D7 Home E7 ABCCC F7 3 C8 43251 D8 Away E8 BCD F8 6 C9 43251 D9 Away E9 BCD F9 6 C10 43251 D10 Away E10 BCD F10 6 C11 43251 D11 Away E11 BCDD F11 6 C12 43251 D12 Away E12 BCDDD F12 6 C13 43251 D13 Away E13 BCDDDD F13 6 C14 43251 D14 Away E14 BCDDDDD F14 6 C15 43251 D15 Away E15 BCDDDDDD F15 6 C16 43252 D16 Home E16 ABC F16 3 C17 43252 D17 Home E17 ABC F17 3 C18 43252 D18 Home E18 ABCC F18 3 C19 43252 D19 Home E19 ABCC F19 3 C20 43252 D20 Home E20 ABCCC F20 3 C21 43252 D21 Home E21 ABCCC F21 3 C22 43252 D22 Away E22 BCD F22 7 C23 43252 D23 Away E23 BCD F23 7 C24 43252 D24 Away E24 BCDD F24 7 C25 43252 D25 Away E25 BCDDD F25 7 C26 43252 D26 Away E26 BCDDDD F26 7 C27 43252 D27 Away E27 BCDDDDD F27 7 C28 43252 D28 Away E28 BCDDDDDD F28 7 C29 43252 D29 Away E29 BCDDDDDDD F29 7 <tbody> </tbody>

<tbody>
</tbody>

Is this output fine?

Home
Prop	5/31/2018	6/1/2018	6/2/2018
ABC	2	2
ABCC	2	2
ABCCC	2	2
BCD	0	0
BCDD	0	0
BCDDD	0	0
BCDDDD	0	0
BCDDDDD	0	0
BCDDDDDD	0	0
BCDDDDDDD	0	0
Away
Prop	5/31/2018	6/1/2018	6/2/2018
ABC	0	0
ABCC	0	0
ABCCC	0	0
BCD	3	2
BCDD	1	1
BCDDD	1	1
BCDDDD	1	1
BCDDDDD	1	1
BCDDDDDD	1	1
BCDDDDDDD	0	1

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Formula Used: =COUNTIFS($E:$E,$K4,$D:$D,"Home",$C:$C,L$3) . K is unique Prop and L is date.

 

[Aladin Akyurek]

@ hullu

Try to post the expected values if the values in F incorrect you have listed are incorrect.

 

[hullu]

The values I have in F are correct, I manually entered them to try getting my point across. So on 5/31 there are 6 entries, but only 3 are unique properties. There will eventually be 17 different locations, and I just want to know how many unique properties are affected since each property can have multiple entries (up to 6). I revised my original table to try and help explain a bit more. On 5/31 property ABC in Region Home had two conditions but since that is only one property I just want it counted once. To give you an idea the full report on 5/31 has 7170 line items, but i know there are only 4600 properties affected. I hope that helps.

	A	B	C	D	E	F
1	Report Date	Region	Prop	Condition	# of Conditions	# of Unique Props
2	5/31/2018	Home	ABC	Cond A	1	3
3	5/31/2018	Home	ABC	Cond B	1	3
4	5/31/2018	Home	ABCC	Cond A	1	3
5	5/31/2018	Home	ABCC	Cond B	1	3
6	5/31/2018	Home	ABCCC	Cond A	1	3
7	5/31/2018	Home	ABCCC	Cond B	1	3
8	5/31/2018	Away	BCD	Cond A	1	6
9	5/31/2018	Away	BCD	Cond B	1	6
10	5/31/2018	Away	BCD	Cond C	1	6
11	5/31/2018	Away	BCDD	Cond A	1	6
12	5/31/2018	Away	BCDDD	Cond A	1	6
13	5/31/2018	Away	BCDDDD	Cond A	1	6
14	5/31/2018	Away	BCDDDDD	Cond A	1	6
15	5/31/2018	Away	BCDDDDDD	Cond A	1	6
16	6/1/2018	Home	ABC	Cond A	1	3
17	6/1/2018	Home	ABC	Cond B	1	3
18	6/1/2018	Home	ABCC	Cond A	1	3
19	6/1/2018	Home	ABCC	Cond B	1	3
20	6/1/2018	Home	ABCCC	Cond A	1	3
21	6/1/2018	Home	ABCCC	Cond B	1	3
22	6/1/2018	Away	BCD	Cond A	1	7
23	6/1/2018	Away	BCD	Cond B	1	7
24	6/1/2018	Away	BCDD	Cond A	1	7
25	6/1/2018	Away	BCDDD	Cond A	1	7
26	6/1/2018	Away	BCDDDD	Cond A	1	7
27	6/1/2018	Away	BCDDDDD	Cond A	1	7
28	6/1/2018	Away	BCDDDDDD	Cond A	1	7
29	6/1/2018	Away	BCDDDDDDD	Cond A	1	7

<tbody>
</tbody>

Sheet1​

 

[hullu]

This can be closed. I was able to get the result I wanted in the PivotTable by adding my data to the DataModel and using the DistinctCount function. Sorry about the hassle.

 

[Aladin Akyurek]

This can be closed. I was able to get the result I wanted in the PivotTable by adding my data to the DataModel and using the DistinctCount function. Sorry about the hassle.

What is the answer? The F values you have posted were the expected values or not?

 

[hullu]

What is the answer? The F values you have posted were the expected values or not?

Yes, that was the expected value. Thanks

 

[Aladin Akyurek]

Yes, that was the expected value. Thanks

In D2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$2:$C$29<>"",IF($A$2:$A$29=$A2,IF($B$2:$B$29=$B2,MATCH($C$2:$C$29,$C$2:$C$29,0)))),ROW($C$2:$C$29)-ROW($C$2)+1),1))