Complex IF formula

[Edie Ritterpusch]

I am using Excel 2013 and trying to create an IF forumula:

I have a drop down list of 32 items in Cell F9.  If they selection one of the first 16 I want to multiply Cell M9 by 1.  If they select one of the second 16 I want to multiply cell L9 by Cell M9.

I tried nesting them:

=IF(F9="Pre-Treat Asphalt",1*M9,IF(F9="Post-Treat Asphalt",1*M9,L9*M9)))

I also tried:

=IF(OR(F9="Pre-Treat Asphalt","Post-Treat Asphalt),"1*M9","L9*M9")

I'm not sure what I'm doing wrong. 

[#2]

I advise you to depend on the source of that drop-down list in the logical test as shown in the below screenshot:

This is the formula:

=IF(F19<>"",IF(OR(F19=A1:A16),M19,L19*M19),"")

To enter it, press Ctrl+Shift+Enter because it's an array formula.

[#3]

You are so Awesome!!!  Thank you so much!!!

[#4]

Okay - it's not working completely.  Please see my attachment.  If the "Service Item" selected is O9:O24, then  "Subtotal" should equal the "Rate".  If the "Service Item" selected is O25:O41, then "Subtotal" should equal "Total Hours" times "Rate".

With this formula, Subtotal will equal Rate only if O9 is selected from the drop down menu for "Service Item".  All other "Service Item" selections (O10:O41) give a Subtotal of "Total Hours" times "Rate".

[#5]

What happened is:

You forget to enter the formula by using the special keystroke shortcut: Ctrl+Shift+Enter.

Select cell N9, press F2 to activate the edit mode, then don't hit Enter, but press Ctrl+Shift+Enter simultaneously, and do this every time you activate the edit mode.

It's an array formula, you have to do this to force it to return the right result.

After that, drag the formula down, but before you drag it, you have to make the range absolute to prevent it from changing during the drag, so just replace this: O9:O24 with this: $O$9:$O$24.

Good luck

[#6]

Given that your drop-down list source is in O9:O41 (which is 33 cells not 32), then you can use the MATCH function to return the position of your F9 selection within the source list.  If the position ios less than 17, then return whatever is in M9, otherwise return M9*L9.

=IF(MATCH(F9,$O$9:$O$41,0)<17,M9,M9*L9)

The 0 in the third argument of the MATCH function specifies an "exact match" which is OK because F9 must be selected from the list in $O$9:$O$41.