Forum Discussion
Complex IF formula
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.
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".
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 https://support.office.com/en-us/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d, 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