If Function #Value! error

i have this formula

=IF(E9="Yes",J9+K9-N9,IF(E9="No",K9-N9))

which works ok but i have had to omit a cell because the formula does not like it as its an empty cell (but has a formula in it)

this is how i would like the formula to look like.

=IF(E9="Yes",J9:L9-N9,IF(E9="No",J9:L9-N9))

but result is #Value

Originally Posted by romperstomper

Assuming you mean sum(J9:L9) rather than J9:L9, isn't that the same formula in both parts of the IF function?

yes but if No is entered there there will be an empty cell but that should not matter should it?

take a look at Full_Time_DI on this workbook O9 is where the formula is

https://www.dropbox.com/s/z9qkgxe2x7...orum.xlsm?dl=0

also change O10 on Full_Time_Contracts to 8 then go back to the other sheet. or just turn E10 to Yes

Could this be what you are trying to do? I'm not sure what your purpose is.
It looks like for yes you want to add J9, K9, L9 and subtract N9. and THEN what you really want is to subtract J9, K9, and L9 and subtract N9?

=IF(E9="Yes",SUM(J9:L9)-N9,IF(E9="No",(J9-K9-L9)-N9))

Semi-colons don't work unless your are using a defined formula that works with a range.

Originally Posted by romperstomper

You just need:
=SUM(J9:L9)-N9

thank you sir