Any Excel Folks Able to Help?
Discussion
I am writing a spread sheet that compares costs, using the SUMIFS formula.
So I have a page that contains the base prices. I then have another page with the specific order details, that uses SUMIFS to select the base price from sheet 1 and applies it to the order, all good.
I then use another SUMIF to multiply the price by the qty to get the cost So if item A is £1, under some conditions it is £1+25% or 50% etc.
All is good, with 2 conditions, so if A is £1 or £1+25% I get the correct answer, but as soon as I add a 3rd condition it sums the result of the conditions. What am i doing wrong?
=SUMIF(I53, "<=A")*J53+SUMIF(I53, ">=B")*(J53*1.25)+SUMIF(I53, ">=C")*(J53*1.5)
So I have a page that contains the base prices. I then have another page with the specific order details, that uses SUMIFS to select the base price from sheet 1 and applies it to the order, all good.
I then use another SUMIF to multiply the price by the qty to get the cost So if item A is £1, under some conditions it is £1+25% or 50% etc.
All is good, with 2 conditions, so if A is £1 or £1+25% I get the correct answer, but as soon as I add a 3rd condition it sums the result of the conditions. What am i doing wrong?
=SUMIF(I53, "<=A")*J53+SUMIF(I53, ">=B")*(J53*1.25)+SUMIF(I53, ">=C")*(J53*1.5)
Unless I've misunderstood what you're trying to achieve (not 100% sure what your "conditions" are), I would do it using vlookups instead...

So for the example above:
Columns J-K are where you set your base prices for each item
Columns M-N are where you set your condition names and mark-up %age
Column B is where you list the items on the order
Column C enter the qty
Column D lookups up the base price
Column E enter the condition (I assume manually??)
Column F looks up the markup %
Column G calculate final price
(rows 11-13 are the same but display formulas rather than calculation results)
So for the example above:
Columns J-K are where you set your base prices for each item
Columns M-N are where you set your condition names and mark-up %age
Column B is where you list the items on the order
Column C enter the qty
Column D lookups up the base price
Column E enter the condition (I assume manually??)
Column F looks up the markup %
Column G calculate final price
(rows 11-13 are the same but display formulas rather than calculation results)
Thanks for the help.
I have never used vlookups before but am going to have a play.
I am trying to check invoices, where a product can vary in cost depending on its type and size. The type affects the base price then the size affect the % increase in cost
So product A can cost £1, £1+20%, £1+50% etc.
I am sure this is clunky, but I can achieve my result by minusing the previous result from the calculation.
I have never used vlookups before but am going to have a play.
I am trying to check invoices, where a product can vary in cost depending on its type and size. The type affects the base price then the size affect the % increase in cost
So product A can cost £1, £1+20%, £1+50% etc.
I am sure this is clunky, but I can achieve my result by minusing the previous result from the calculation.
sideways sid said:
Zetec-S has addressed your question well, but Index(Match) is a much better solution for dynamic data than Vlookup, and a much better discipline to get your head around OP.
good point, I've used vlookup for years so it's my "go-to" choice, but am trying to get in the habit of using index(match) as I agree it's a lot better.Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


