Excel Formula Assistance

[Onihonker]

I have to calculate door pricing for my company and have no idea how to create the key formula to make it all work.

I have a table that holds the base price and depending on how the door gets finished determines the sales total. I know how to make IF statements in excel but I don't know how to link multiple conditions together.

I don't know how to write the formula if certain fields are yes or no, if the field is yes I would like it to add the additional amount and then finally total all the boxes with a "Y" and give a sales total.

Example.
Base Cost / PST 7% / Summit Door +3 / Alder +4 / White +2/ Sales Total
100 *1.07 Y N Y 105

I can get the IF portion to check if the box has a y but cant get it to total additional fields with y value.

 

[marleymanner1]

Hello there.

This is just a shot in the dark at this, but I would say if you want to perform calculations based on a condition, something like the following would work fine:

=if(field1 = "Y", "number to add here", 0) + (or *) if(next condition here) - if(next condition here)

etc, etc...

 

[qafself]

Suggest that you use Vlookup to add in the various components if a Yes exists.

If you post an example of your workbook it would be easier to advise.....

 

[Brianwarnock]

I didn't really understand your example but if base cost is in col a summitdoor col b etc then a formulae like

=Sum((a2*1.07)+if(b2="Y",3,0)+if(c2="Y",4,0)+.....

should work.

Brian

 

[Onihonker]

Here is a workbook example:

 

[qafself]

Try this.

I have altered the layout a bit and created a pricelist to look up the values

 

[Onihonker]

Thank you, As I'm off for remembrance day and don't have excel at home I'll have to try it at work on Wednesday.

 

[Brianwarnock]

If you alter your sheet to show the prices in row 2 then sumproduct will give you the answer

=C4+SUMPRODUCT((D4:I4="Y")*(D$2:I$2))

Brian