Case sensitive distinct count based on multiple criteria

[kjd513]

Hello,
Can you please help me with finding the count of distinct (case sensitive) products sold (Column B) per store (Column C) as per below data? Product_sold column has data from B2: B10. Store column has data from C2:C10. F2:F5 has the resulting expected values that the formula should calculate.
In Column F2, I want a formula which counts the distinct count (case sensitive) of product sold for the store "Costco". The answer should calculate to 2 in F2. Is there a way using SUMPRODUCT and EXACT to calculate distinct count(including case sensitive data) in Column B corresponding to the values in Column C?

I have the below formula which only looks up the range of B2:B10 and counts the occurrences of the value in B2. However, I am not able to expand this formula to make it look for the corresponding values in Column C and then calculate the distinct (case sensitive) count in Column B based on the value from Column C.

=SUMPRODUCT(--EXACT($B$2:$B$10,B2)) --> Is there a way to expand this formula maybe using IF statement and make the formula lookup the range in Column C?

	A	B	C	D	E	F
1	Employee_id	Product_sold	Store		Store	Count of distinct(case sensitive) products sold per store
2	1	Apples	Costco		Costco	2
3	1	APPLES	Costco		Stop and Shop	2
4	2	APPLES	Costco		Trader Joes	1
5	2	oranges	Stop and Shop		Amazon	2
6	2	Oranges	Stop and Shop
7	2	GraPES	Amazon
8	3	grapes	Amazon
9	4	Kiwis	Trader Joes
10	4	kiwis	Amazon

 

[StephenCrump]

Welcome to the Forum!

Try F2: =IFERROR(ROWS(REDUCE(,FILTER(B$2:B$10,C$2:C$10=E2),LAMBDA(a,b,IF(SUM(--EXACT(a,b)),a,VSTACK(a,b))))),"-")

 

[shaowu459]

try this:

工作簿1
	A	B	C	D	E	F	G	H	I	J
1	Employee_id	Product_sold	Store		Store	Count of distinct(case sensitive) products sold per store
2	1	Apples	Costco		Costco	2	2	2
3	1	APPLES	Costco		Stop and Shop	2	2	2
4	2	APPLES	Costco		Trader Joes	1	1	1
5	2	oranges	Stop and Shop		Amazon	3	3	3
6	2	Oranges	Stop and Shop
7	2	GraPES	Amazon
8	3	grapes	Amazon
9	4	Kiwis	Trader Joes
10	4	kiwis	Amazon
11
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=ROWS(UNIQUE(BYROW(EXACT(B$1:B$10,TOROW(B$1:B$10))*(C$1:C$10=E2),CONCAT)))-1
H2:H5	H2	=ROWS(UNIQUE(BYROW(EXACT(B$1:B$10,TOROW(B$1:B$10))*(C$1:C$10=E2),LAMBDA(x,CONCAT(x)))))-1