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!

 

[Aladin Akyurek]

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!

Would you post a 10-row worth of data?

 

[tusharm]

Create a PivotTable (Data | PivotTable and PivotChart Report...) with Product and Size as the row fields and Price as the data field. Excel will probably show Sum of Price as the value. Right-click on the data field header select 'Field Settings...' and change the Sum to Min.

You'll get the minimum prices for all products and all sizes with this single step.

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!

 

[brianclong]

Hello. Here is the data. I would like a formula to find this as I will create a recurring report with this and other measures.

Product Size Price
A 1L 100
A 2L 100
A 1L 5
A 1L 100
B 1L 100
A 2L 100
B 1L 100
B 2L 100

I would like to find the smallest price for Product A, 1L, which would be 5 in the example.

Thanks for any help!

 

[Peter_SSs]

Columns E:G shows the minimum price for each product and size using the PivotTable idea.

Columns I:J show how you can find individual minimum prices by formula. Note that the formula should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter, and that will insert the {} around the formula.

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

 

[brianclong]

Perfect! Thanks much!!

Quick question, I noticed when I did not press CSE the answer was the same. Is that just an anomaly or do I need to make this a CSE formula (just clarifying because I know array formulas take extra computation power).

 

[Peter_SSs]

Perfect! Thanks much!!

Quick question, I noticed when I did not press CSE the answer was the same. Is that just an anomaly or do I need to make this a CSE formula (just clarifying because I know array formulas take extra computation power).

Coincidence. To test, try this:

1. Change the Size of Interest to 2L
2. Click in the formula cell, press F2 then Enter. This will remove the {}.

The formula should now give you an incorrect result.

 

[brianclong]

Ah got it! Thanks again! Nice formula!

 

[brianclong]

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!

 

[brianclong]

Never mind, I just added a column that put a "1" if the value was greater than zero and based a condition off that. Thanks again!