SUMPRODUCT working in argument window, but returns error in cell

[improvet]

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?

 

[oldbrewer]

what are you trying to do with row 1 and row 2

a2-a1 plus b2-b1 etc ?

 

[RoryA]

Did you array enter the formula using Ctrl+Shift+Enter rather than just Enter?

 

[FormR]

Hi, welcome to the forum!

You can't use a IF() statement like that without also requiring the formula to be committed with CSE.

You could try like this instead:

=SUMPRODUCT(1-(ISEMPTY(A2:E2));A2:E2-A1:E1*50)

 

[steve the fish]

ISEMPTY is a VBA function so isn't going to work on a sheet. You want something like:

=SUMPRODUCT(A2:E2-((A2:E2<>"")*A1:E1*50))

 

[Aladin Akyurek]

Maybe: Control+shift+enter, not just enter...

=SUM(IF(ISNUMBER(A2:E2*A1:E1),A2:E2-A1:E1*50))

 

[improvet]

Re: SUMPRODUCT working in argument window, but returns error in cell [Solved]

Did you array enter the formula using Ctrl+Shift+Enter rather than just Enter?

This worked, completely forgot that it is needed in some cases, thanks!

 

[improvet]

ISEMPTY is a VBA function so isn't going to work on a sheet. You want something like:

=SUMPRODUCT(A2:E2-((A2:E2<>"")*A1:E1*50))

I translated it from another language, the correct translation is ISBLANK(), not ISEMPTY(), srry about that.

 

[Aladin Akyurek]

Try to use ISNUMBER, not ISBLANK, because it would be appropriate for numeric data.

 

[improvet]

Hi, welcome to the forum!

You can't use a IF() statement like that without also requiring the formula to be committed with CSE.

You could try like this instead:

=SUMPRODUCT(1-(ISEMPTY(A2:E2));A2:E2-A1:E1*50)

This worked as well without the need of ctrl + shift + enter. Think I'll go with this one!


Thanks a lot for all the quick answers!
(just realized I answered all you guys in separate posts, but couldn't find the edit button so couldn't change it to 1 post)