Combine SUMIF and MIN functions?

[brodprocan]

Hello all,
I'm brand new to this forum (in terms of posting), as I can usually find the answers to my questions by searching other people's posts. But not this time, so I'm hoping somebody can help.

I'm trying to combine the SUMIF and MIN functions, but can't get it to work. As an example of what I want to achieve, see the worksheet below. I have rows of products, and columns of suppliers. Some of the products are supplied by various suppliers, although at different prices. I've used the MIN function in conditional formatting to highlight the cheapest price for each product (i.e. in each row):

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Item to be purchased[/TD]
[TD="align: center"]Supplier A[/TD]
[TD="align: center"]Supplier B[/TD]
[TD="align: center"]Supplier C[/TD]
[TD="align: center"]Supplier D[/TD]
[/TR]
[TR]
[TD]Product A[/TD]
[TD="align: center"]$10.00[/TD]
[TD="align: center"]$12.00[/TD]
[TD="align: center"]$15.00[/TD]
[TD="align: center"]$14.00[/TD]
[/TR]
[TR]
[TD]Product B[/TD]
[TD="align: center"]$25.00[/TD]
[TD="align: center"]$22.00[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$26.00[/TD]
[/TR]
[TR]
[TD]Product C[/TD]
[TD="align: center"]$35.00[/TD]
[TD="align: center"]$34.00[/TD]
[TD="align: center"]$32.00[/TD]
[TD="align: center"]$40.00[/TD]
[/TR]
[TR]
[TD]Product D[/TD]
[TD="align: center"]$28.00[/TD]
[TD="align: center"]$30.00[/TD]
[TD="align: center"]$31.00[/TD]
[TD="align: center"]$32.00[/TD]
[/TR]
[TR]
[TD]Product E[/TD]
[TD="align: center"]$55.00[/TD]
[TD="align: center"]$54.00[/TD]
[TD="align: center"]$56.00[/TD]
[TD="align: center"]$53.00[/TD]
[/TR]
[TR]
[TD]Total spend per supplier[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[TD="align: center"]SUMIF (Lowest price)[/TD]
[/TR]
</tbody>[/TABLE]


This is a simplified version, my actual spreadsheet is much bigger, but what I want to do is SUM only the lowest prices under each supplier. I've tried combining the SUMIF and the MIN function in various ways, but I keep getting a zero, or a #VALUE error. Is this because the SUMIF is working down the column while the MIN is working across each row? Is there a better way to do this? With formulas preferably, I'd rather not get into VBA.

Thanks in advance,

 

[pgc01]

You're welcome. I'm glad it helped.
Thanks for the follow up. Will help others with a similar problem.

 

[brodprocan]

Hi again PGC,
Not sure if I should start a new thread for this, or continue with this one, but I've developed my spreadsheet a little further, and have now added extra worksheets for different projects. Each of these worksheets will serve as a kind of invoice for each project, so I don't want to copy across all the pricing information from the original worksheet, but would rather just provide a total cost of items used by looking up the prices in the master sheet. I thought INDEX MATCH functions would be best for this, but can't seem to get the syntax right.

My 'invoice' worksheet would include a list of items used, a list of quantities required, and then a total cost of items:

3 x Product A
2 x Product B
1 x Product C
4 x Product D
2 x Product E

Total cost of materials = ?

How can I use INDEX MATCH with the formula that you created:
=SUMPRODUCT(--(SUBTOTAL(5,OFFSET($E$4:$H$8,ROW($E$4:$E$8)-MIN(ROW($E$4:$E$8)),,1))=E4:E8),E4:E8)

to lookup the lowest price for each product listed on the 'invoice'? (Which I would then multiply by the quantities and sum them all up).

 

[Marcelo Branco]

I think pgc isn't online, so let me try to help you.

To get the MIN for each product you can use a much simpler formula. Something like:
=MIN(INDEX(range, MATCH(product,product_column,0),0))
Observe the 0 in the third argument of INDEX - it makes INDEX return an array of values, i.e., all the values of the row correspondent to the product found by MATCH in product_column.

That said, assuming data sample provided by pgc in post #5 (data in Sheet1, columns D:H, rows 3:8) try in another sheet


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Product​

[/TD]
[TD]

Min
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Product A​

[/TD]
[TD]

10​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Product B​

[/TD]
[TD]

22​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Product C​

[/TD]
[TD]

32​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Product D​

[/TD]
[TD]

28​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Product E​

[/TD]
[TD]

53​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied down
=MIN(INDEX(Sheet1!$E$4:$H$8,MATCH(A2,Sheet1!$D$4:$D$8,0),0))

Hope this helps

M.

 

[brodprocan]

Hello Marcelo,
Thank you for your response. What you've suggested certainly helps to find the lowest price for each item, and I did manage to achieve this previously in a much more convoluted way, so your suggestion will help to simplify it. However, rather than list the price of each item and then sum them all up in another field, I was hoping to have one formula that does all of this in the background, and then just show the total figure. I've tried adding SUM, SUMPRODUCT, and SUBTOTAL(9 ... to your equation, but these don't seem to work. Have also tried adding the OFFSET function as PGC suggested, but can't get this to work either.

I suppose I could calculate the individual amounts as you've suggested, hide that column, and then only show the total amount, but in the interests of increasing my knowledge and understanding, I'd like to know if it's possible to do it in a single equation.

Thanks again,
Broderick

 

[Marcelo Branco]

Hi Broderick

If you do want the sum of all the lowest prices you can simplify pgc's formula, keeping only the relevant parts for such objective.

Assuming data sample of post #5 in Sheet1 try on another sheet
=SUMPRODUCT(SUBTOTAL(5,OFFSET(Sheet1!$E$4:$H$8,ROW(Sheet1!$E$4:$E$8)-MIN(ROW(Sheet1!$E$4:$E$8)),,1)))

that returns 145, that is the sum of the lowest prices (10+22+32+28+53)

M