Count lowest price per two arguments.

[knaabis]

I need to count for which store and how many this store has the lowest price for each product?
For example result - Shop1=0, Shop2=1, Shop3=2

Prod. Shop Price
Apple Shop1 5
Apple Shop2 3
Apple Shop3 4

Pears Shop1 8
Pears Shop2 7
Pears Shop3 6

Lemon Shop1 2
Lemon Shop2 3
Lemon Shop3 1

 

[Gerald Higgins]

I would use a helper column, to identify for each product which store has the lowest prices.
Then use a SUMIF function on the results, to count how often stores have the lowest prices.

Watch out for situations where two or more shops both have the same lowest prices for the same product.

 

[knaabis]

I would use a helper column, to identify for each product which store has the lowest prices.

How?

 

[AlanY]

Cols E & F work out the min for each product and location
Col H count them

Book1
	A	B	C	D	E	F	G	H
1	Prod.	Shop	Price
2	Apple	Shop1	5	Apple	3	Shop2	Shop1	0
3	Apple	Shop2	3	Pears	6	Shop3	Shop2	1
4	Apple	Shop3	4	Lemon	1	Shop3	Shop3	2
5
6	Pears	Shop1	8
7	Pears	Shop2	7
8	Pears	Shop3	6
9
10	Lemon	Shop1	2
11	Lemon	Shop2	3
12	Lemon	Shop3	1
Sheet3

Cell Formulas
Range	Formula
H2	=COUNTIF(F:F,G2)
E2	{=SMALL(IF($A$2:$A$12=D2,$C$2:$C$12),1)}
F2	{=INDEX($B$2:$B$12,MATCH(D2&E2,$A$2:$A$12&$C$2:$C$12,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[knaabis]

Thanks a lot!

 

[AlanY]

you're welcome