Two-condition If formula

Hello again!

I am creating a IF(... formula, here is what i have so far...

=IF(B3='Max' AND C3='Sell', D3-J17,0)

I have two drop down boxes, one at B3 and one at C3, i want to D3-J17, but only when B3 = Max and C3 = Sell, otherwise 0 (really instead of 0 im gonna add in more formula for the rest of the drop downs, but for arguments sake lets say 0), what do i do?

Thanks!
Max

ah brillo thanks!!

Ok, let me see if I can do this quickly/simply.

Make your first drop box, right-click it, pick 'format control', then go to the 'control' tab. You'll have to define an 'input range' - this is the list of choices you want the user to select from in the drop box. For the sake of this example, I'm putting the list as follows in A1:A3 (it could be anywhere on this or another sheet):

Max
Min
Avg

Next, you have to select the 'cell link' - this is where the result of the user's choice will be stored - I set this to cell B3, per your example/request). Note that this is not the value itself (i.e. it won't display the text "Max"), it is the numeric spot on the A1:A3 list. Choosing 'Max' from the drop box will result in a 1 in cell B3.

You'll have to do this for the second drop box as well, with it's own input range and cell link as C3, per your example...

This will leave you with numeric values in cells B3 and C3.

From there, your formula could/would be (assuming that 'Max' is the first item in your list, and 'Sell' is the first item in the second list, therefore both putting a numeric value of '1' in cells B3 and C3, respectively):

=IF(AND(B3=1,C3=1),D3-J17,0)

Does that help?

Originally Posted by NBVC

Try:

=IF(AND(B3="Max",C3="Sell"), D3-J17,0)

How does that work if he's using drop-boxes? Values in B3 and C3 won't actually be "Max" and "Sell", respectively; they'll be a numeric value... Won't they? Maybe I missed something.

Originally Posted by NBVC

I assumed (and looks like I was right based on the OP's response) that he meant Data Validation drop down... not Forms/Controls drop downs...

Ooh, good point - well done.

hehe thats works, you are an excel god !