Find smallest number with multiple criteria

[brianclong]

I need a formula that will give me the smallest number based on multiple criteria.

For example, I want to find all:

1. Product A
2. 1 Liter
3. Smallest Price

In other words, out of all 1 Liter Product A's, I want to see the smallest price point.

I tried a sumproduct but I can't get it to work. Something like =sumproduct((A1:A100="Product A")*(B1:B100="1L")*(small(C1:C100,1)))

How can I get the small formula working within the sumproduct? Or is there another workaround?

Any help would be great!

 

[Peter_SSs]

Oh no! I need a little more help. The formula is pulling in zeros. I tried to add *(C2:C9>0) but it is still bringing in zeros. Any advice? Thanks!

You shouldn't need a helper column and this should have worked - did you remember to re-confirm with Ctrl+Shift+Enter? Here it is working with blank and zero:

Excel Workbook
	A	B	C	D	H	I	J
1	Product	Size	Price			Product of Interest	A
2	A	1L	0			Size of Interest	1L
3	A	2L	100			Min Price	5
4	A	1L	5
5	A	1L
6	B	1L	100
7	A	2L	100
8	B	1L	100
9	B	2L	100
10
Min Price

 

[brianclong]

Thanks Peter. This is great stuff!