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

hullu

New Member
Joined
Jun 1, 2018
Messages
4
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))


CDEF
1Report DateLocationPropUnique Value Per Location per Date
25/31/2018HomeABC3
35/31/2018HomeABC3
45/31/2018HomeABCC3
55/31/2018HomeABCC3
65/31/2018HomeABCCC3
75/31/2018HomeABCCC3
85/31/2018AwayBCD6
95/31/2018AwayBCD6
105/31/2018AwayBCD6
115/31/2018AwayBCDD6
125/31/2018AwayBCDDD6
135/31/2018AwayBCDDDD6
145/31/2018AwayBCDDDDD6
155/31/2018AwayBCDDDDDD6
166/1/2018HomeABC3
176/1/2018HomeABC3
186/1/2018HomeABCC3
196/1/2018HomeABCC3
206/1/2018HomeABCCC3
216/1/2018HomeABCCC3
226/1/2018AwayBCD7
236/1/2018AwayBCD7
246/1/2018AwayBCDD7
256/1/2018AwayBCDDD7
266/1/2018AwayBCDDDD7
276/1/2018AwayBCDDDDD7
286/1/2018AwayBCDDDDDD7
296/1/2018AwayBCDDDDDDD7

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
C1Report Date
D1Location
E1Prop
F1Unique Value Per Location per Date
C243251
D2Home
E2ABC
F23
C343251
D3Home
E3ABC
F33
C443251
D4Home
E4ABCC
F43
C543251
D5Home
E5ABCC
F53
C643251
D6Home
E6ABCCC
F63
C743251
D7Home
E7ABCCC
F73
C843251
D8Away
E8BCD
F86
C943251
D9Away
E9BCD
F96
C1043251
D10Away
E10BCD
F106
C1143251
D11Away
E11BCDD
F116
C1243251
D12Away
E12BCDDD
F126
C1343251
D13Away
E13BCDDDD
F136
C1443251
D14Away
E14BCDDDDD
F146
C1543251
D15Away
E15BCDDDDDD
F156
C1643252
D16Home
E16ABC
F163
C1743252
D17Home
E17ABC
F173
C1843252
D18Home
E18ABCC
F183
C1943252
D19Home
E19ABCC
F193
C2043252
D20Home
E20ABCCC
F203
C2143252
D21Home
E21ABCCC
F213
C2243252
D22Away
E22BCD
F227
C2343252
D23Away
E23BCD
F237
C2443252
D24Away
E24BCDD
F247
C2543252
D25Away
E25BCDDD
F257
C2643252
D26Away
E26BCDDDD
F267
C2743252
D27Away
E27BCDDDDD
F277
C2843252
D28Away
E28BCDDDDDD
F287
C2943252
D29Away
E29BCDDDDDDD
F297

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Sahitya

New Member
Joined
May 29, 2018
Messages
27
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))


CDEF
1Report DateLocationPropUnique Value Per Location per Date
25/31/2018HomeABC3
35/31/2018HomeABC3
45/31/2018HomeABCC3
55/31/2018HomeABCC3
65/31/2018HomeABCCC3
75/31/2018HomeABCCC3
85/31/2018AwayBCD6
95/31/2018AwayBCD6
105/31/2018AwayBCD6
115/31/2018AwayBCDD6
125/31/2018AwayBCDDD6
135/31/2018AwayBCDDDD6
145/31/2018AwayBCDDDDD6
155/31/2018AwayBCDDDDDD6
166/1/2018HomeABC3
176/1/2018HomeABC3
186/1/2018HomeABCC3
196/1/2018HomeABCC3
206/1/2018HomeABCCC3
216/1/2018HomeABCCC3
226/1/2018AwayBCD7
236/1/2018AwayBCD7
246/1/2018AwayBCDD7
256/1/2018AwayBCDDD7
266/1/2018AwayBCDDDD7
276/1/2018AwayBCDDDDD7
286/1/2018AwayBCDDDDDD7
296/1/2018AwayBCDDDDDDD7

<tbody>
</tbody>
Sheet5

Worksheet Formulas
CellFormula
C1Report Date
D1Location
E1Prop
F1Unique Value Per Location per Date
C243251
D2Home
E2ABC
F23
C343251
D3Home
E3ABC
F33
C443251
D4Home
E4ABCC
F43
C543251
D5Home
E5ABCC
F53
C643251
D6Home
E6ABCCC
F63
C743251
D7Home
E7ABCCC
F73
C843251
D8Away
E8BCD
F86
C943251
D9Away
E9BCD
F96
C1043251
D10Away
E10BCD
F106
C1143251
D11Away
E11BCDD
F116
C1243251
D12Away
E12BCDDD
F126
C1343251
D13Away
E13BCDDDD
F136
C1443251
D14Away
E14BCDDDDD
F146
C1543251
D15Away
E15BCDDDDDD
F156
C1643252
D16Home
E16ABC
F163
C1743252
D17Home
E17ABC
F173
C1843252
D18Home
E18ABCC
F183
C1943252
D19Home
E19ABCC
F193
C2043252
D20Home
E20ABCCC
F203
C2143252
D21Home
E21ABCCC
F213
C2243252
D22Away
E22BCD
F227
C2343252
D23Away
E23BCD
F237
C2443252
D24Away
E24BCDD
F247
C2543252
D25Away
E25BCDDD
F257
C2643252
D26Away
E26BCDDDD
F267
C2743252
D27Away
E27BCDDDDD
F277
C2843252
D28Away
E28BCDDDDDD
F287
C2943252
D29Away
E29BCDDDDDDD
F297

<tbody>
</tbody>

<tbody>
</tbody>


Is this output fine?


Home
Prop5/31/20186/1/20186/2/2018
ABC22
ABCC22
ABCCC22
BCD00
BCDD00
BCDDD00
BCDDDD00
BCDDDDD00
BCDDDDDD00
BCDDDDDDD00
Away
Prop5/31/20186/1/20186/2/2018
ABC00
ABCC00
ABCCC00
BCD32
BCDD11
BCDDD11
BCDDDD11
BCDDDDD11
BCDDDDDD11
BCDDDDDDD01




<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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
@ hullu

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

hullu

New Member
Joined
Jun 1, 2018
Messages
4
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.

ABCDEF
1Report DateRegionPropCondition# of Conditions# of Unique Props
25/31/2018HomeABCCond A13
35/31/2018HomeABCCond B13
45/31/2018HomeABCCCond A13
55/31/2018HomeABCCCond B13
65/31/2018HomeABCCCCond A13
75/31/2018HomeABCCCCond B13
85/31/2018AwayBCDCond A16
95/31/2018AwayBCDCond B16
105/31/2018AwayBCDCond C16
115/31/2018AwayBCDDCond A16
125/31/2018AwayBCDDDCond A16
135/31/2018AwayBCDDDDCond A16
145/31/2018AwayBCDDDDDCond A16
155/31/2018AwayBCDDDDDDCond A16
166/1/2018HomeABCCond A13
176/1/2018HomeABCCond B13
186/1/2018HomeABCCCond A13
196/1/2018HomeABCCCond B13
206/1/2018HomeABCCCCond A13
216/1/2018HomeABCCCCond B13
226/1/2018AwayBCDCond A17
236/1/2018AwayBCDCond B17
246/1/2018AwayBCDDCond A17
256/1/2018AwayBCDDDCond A17
266/1/2018AwayBCDDDDCond A17
276/1/2018AwayBCDDDDDCond A17
286/1/2018AwayBCDDDDDDCond A17
296/1/2018AwayBCDDDDDDDCond A17

<tbody>
</tbody>
Sheet1
 

hullu

New Member
Joined
Jun 1, 2018
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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))
 

Forum statistics

Threads
1,136,327
Messages
5,675,127
Members
419,551
Latest member
thangxpm

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
Top