Counting Unique Values with variables

[Tauon]

I'm trying to count how many times unique values show in a column tied to a particular criteria. I need to count how many items a particular location has, but only count the item once even if it repeats over a range of dates:

Example

Location	Count of Values		Location	Values
Store 1	?		Store 1	Value 1
Store 2	?		Store 1	Value 1
Store 3	?		Store 1	Value 2
			Store 1	Value 2
			Store 1	Value 2
			Store 2	Value 1
			Store 2	Value 1
			Store 2	Value 2
			Store 2	Value 2
			Store 2	Value 2
			Store 3	Value 1
			Store 3	Value 1
			Store 3	Value 2
			Store 3	Value 2
			Store 3	Value 2

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1	Location	Count of Values		Location	Values
2	Store 1	2		Store 1	Value 1
3	Store 2	2		Store 1	Value 1
4	Store 3	2		Store 1	Value 2
5				Store 1	Value 2
6				Store 1	Value 2
7				Store 2	Value 1
8				Store 2	Value 1
9				Store 2	Value 2
10				Store 2	Value 2
11				Store 2	Value 2
12				Store 3	Value 1
13				Store 3	Value 1
14				Store 3	Value 2
15				Store 3	Value 2
16				Store 3	Value 2
Main

Cell Formulas
Range	Formula
B2:B4	B2	=ROWS(UNIQUE(FILTER($E$2:$E$16,$D$2:$D$16=A2)))

 

[6StringJazzer]

In Excel 365 you can use this formula.

$scratch.xlsm
	A	B	C	D	E
1	Location	Count of Values		Location	Values
2	Store 1	3		Store 1	Value 1
3	Store 2	2		Store 1	Value 1
4	Store 3	2		Store 1	Value 2
5				Store 1	Value 2
6				Store 1	Value 3
7				Store 2	Value 1
8				Store 2	Value 1
9				Store 2	Value 2
10				Store 2	Value 2
11				Store 2	Value 2
12				Store 3	Value 1
13				Store 3	Value 1
14				Store 3	Value 2
15				Store 3	Value 2
16				Store 3	Value 2
Unique Count

Cell Formulas
Range	Formula
B2:B4	B2	=COUNTA(UNIQUE(IF($D$2:$D$16=A2,E2:E16,"")))-1

 

[Tauon]

This one worked! Thank you!

 

[Fluff]

Are you saying that mine didn't?

 

[Tauon]

Are you saying that mine didn't?

It worked on the smaller data set in the example, but didn't work on the actual set I was pulling for some reason.

 

[Fluff]

In what way didn't it work?

 

[Tauon]

In what way didn't it work?

It didn't return the exact data I needed. When I checked it against my locations, the numbers were off. Not sure why.

 

[Fluff]

Ok. Glad you got a solution that worked.

 

[6StringJazzer]

That's odd. I thought Fluff's solution was actually better than mine. I'm surprised it didn't work.

(BTW the "-1" in mine was to take into account that the array would include the null string as a counted value.)