SUMIF

[Kmahraz]

Hi:
I was hoping I can get some help.
looking to use a SUMIF or Vlookup or somthing similar to solve this.
I have Column "C" with several values
Columns E, F, G....L have prices based on qty
Column N,O,P and Q have some additional cost

I need to be able to have a formula that will look at the QTY in column C and do the some of either E or F or G....or L with either N,O,P and Q.

the challenge is that the QTY in column C value and are not exact match to the qty price in column E, F, G....L and N,O,P and Q.

Example:
if the value of C4=24 It need to pick column E (25pcs price) + column N (25 pcs Cost)
if the value of C4=89 It need to pick column G (100pcs price) + column P (100 pcs Cost)
if the value of C4=653 It need to pick column K (700pcs price) + column Q (250 pcs Cost)

Please see sample file below.

Any help will be much appreciated .

Thank you

K

 

[David Evans]

For each example, explain precisely which numbers you want to select from the table. Currently, your Example is confusing as you don't specify which row you want to choose, although you do cite Row 4 for each Qty value. Also, on your worksheet the formulas in Columns S differ from what you've written above - btw, the above make more sense!

I've taken the liberty of making your data into a Table and uploaded it ... I've remove the merged cells as they are a source of errors, and should be avoided whenever possible, and it's always possible to avoid merging ....

 

[Kmahraz]

Hi D,
I apology if I didn't explain my need very well.
You are correct the desired output should be the sum of both yellow cells.

each time the value of each of the cell in the C Column change it should pick the correct cells

So if C9 = 653 then it should sum the closest value to 653, and that's cell J9+O9
Please let me know if this make sens.

regards

K

 

[Khalid NGO]

Hi Karim,
I have un-merged your headers, and replaced the text with numbers, so can be calculated easily.

for price:
=MIN(IF($E$2:$L$2>=$C4,$E4:$L4))
{array formula} to be entered with CSE

Same logic can be applied for the cost part, but there are just 4 slabs and I am not getting closest values for all with this CSE:
=MIN(IF($N$2:$Q$2>=$C4,$N4:$Q4))

Regards,

PS: cells are manually highlighted

 

[Kmahraz]

Hi Khalid,
Thank you so much for the help, I may not explained my request properly.
The values in the demand will constantly change depending on the customer demand.

For example when I change the value of C4 from 24 to 45 it should sum F4 and O4 because they are in the column that has 50 pcs ...

If the value of C4 become 300, it should sum I4 and Q4

Not sure if this make sens or even if it's feasible ...



Regards,
K

 

[Nebu]

Hi:

Is this what you are looking for? I am not entirely clear with your explanation.

Thanks

 

[bosco_yip]

My proposal, without Helper Row,

In S4, array formula (Confirm entered with SHIFT+CTRL+ENTER instead of just ENTER) copy down :

=LOOKUP(C4,IFERROR(LEFT(D$2:K$2,FIND(" ",D$2:K$2)-1)+1,0),E4:L4)+LOOKUP(C4,IFERROR(LEFT(M$2: P$2,FIND(" ",M$2: P$2)-1)+1,0),N4:Q4)

Edit : my formula results appeared in a bit of difference from Nebu's results.

All example results are in same, except Cell S9, criteria C9 =450, my formula result : 56.01 , while Nebu's result : 54.65
Regards

 

[Awesome Wally]

Hi Kmahraz,
I followed the same logic as Nebu for the additional cost but used a sumproduct formula for the Price component. I guessed the price would only be good up to or equal to the Piece price. So a demand of 51 would have to go up to the 100 piece price. But i made sense to have additional cost reflected on the closet number. Hope that helps

 

[Kmahraz]

Thank you all for the great solutions suggested.