Any Excel Folks Able to Help?
Author
Discussion

nikaiyo2

Original Poster:

5,980 posts

226 months

Friday 19th October 2018
quotequote all
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)


SidJames

1,399 posts

264 months

Friday 19th October 2018
quotequote all
There's more than one way to do it.

You need at least one IF statement at the front to ask if conditions A "AND"B are met, if not do condition C.

Zetec-S

6,953 posts

124 months

Friday 19th October 2018
quotequote all
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)

nikaiyo2

Original Poster:

5,980 posts

226 months

Friday 19th October 2018
quotequote all
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.

sideways sid

1,467 posts

246 months

Sunday 21st October 2018
quotequote all
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.

Zetec-S

6,953 posts

124 months

Monday 22nd October 2018
quotequote all
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.
yes 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.