Hi!
I have two rows with values and I'm using sumproduct to subtract the two. However I also need the formula to ignore empty fields and don't do the substraction if the lower field is empty. It seems to work when i use the insert function window, but once the formula is applied to a cell it returns the error #VALUE
=SUMPRODUCT(IF(ISEMPTY(A2:E2);0;A2:E2-A1:E1*50))
The answer should be 1,5*50-60=-15 and then ignoring C1.
And indeed thats what it says in insert formula window:
Any ideas?
I have two rows with values and I'm using sumproduct to subtract the two. However I also need the formula to ignore empty fields and don't do the substraction if the lower field is empty. It seems to work when i use the insert function window, but once the formula is applied to a cell it returns the error #VALUE
=SUMPRODUCT(IF(ISEMPTY(A2:E2);0;A2:E2-A1:E1*50))
![KJz6qUV.png](/board/proxy.php?image=http%3A%2F%2Fi.imgur.com%2FKJz6qUV.png&hash=93aedeaf0f473ad16945b0b16545f82e)
The answer should be 1,5*50-60=-15 and then ignoring C1.
And indeed thats what it says in insert formula window:
![QdJeglN.png](/board/proxy.php?image=http%3A%2F%2Fi.imgur.com%2FQdJeglN.png&hash=ab14faf588d4015633dca5b3fadcfb69)
Any ideas?