If quantity is 'this' and list choice is 'that' then ...

[kfalconer]

Hi,

I am trying to add a function/formula that will make a calculation based on a quantity in one cell and whether "dark" or "white" is chosen from a drop down list in another cell. If the quantity is<13 and the color=white then B8*(C18+C19), If quantity is<13 and the color=dark then B8*(C18+C19+1).

Here is what I have - and it is wrong -

=IF(AND(B8<13,B16=white),B8*(C18+C19),0),IF(AND(B8<13,B16=dark),B8*(C18+C19+1),0)

I am also attaching the spreadsheet. I'm using Excel 2004.

Thanks for any help,
Kate

[teylyn]

Text needs to be enclosed in quotes.
You can nest IF statements, but not concatenate them, i.e your first IF statement closes too early and instead of the 0 use the next IF statement.


try

=IF(AND(B8<13,B16="white"),B8*(C18+C19),IF(AND(B8<13,B16="dark"),B8*(C18+C19+1),0))

cheers

[Marcol]

Try

=SUM(IF(AND(B8<13,B16="white"),B8*(C18+C19),0),IF(AND(B8<13,B16="dark"),B8*(C18+C19+1),0))

[teylyn]

Marcol, why SUM()? It just needs to be put in proper nested IF syntax.

[kfalconer]

Teylyn - Thank you!! I literally have been changing things for hours to get it right. I was so close, yet so far. Your suggestions fixed the problem.
Thank you again -

[Marcol]

Apologies teylyn

A quick-fix without thinking.

Thanks for pointing that out.

[teylyn]

kfalconer, Marcol's solution works as well, it's just not the most elegant approach. But since your formula was basically two IF statements concatenated with a comma, wrapping them into a SUM() is a possibility (after fixing the text string)

So, Marcol, apologies not required.

cheers