SUMPRODUCT returns #Value when cell has an empty string (="")

I would like to use SUMPRODUCT function for the following scenario

A    B
1    ""
2    1

where the value "" was entered using the formula ="" as part of an IF condition like this: IF (condition_is_true, value, ""). I would like to visualize in my table when I have an empty value from zero value, that is why in the IF statement I am using "" instead of 0(that would produce the right result).

I am using SUMPRODUCT as follow:

SUMPRODUCT(A1:A2, B1:B2)

expecting to return the value 2

For excluding the "" cells, I was expecting that the following should work:

SUMPRODUCT(A1:A2, --(B1:B2<>""), B1:B2)

but in this case, it doesn't work because the cell is not blank, I was trying several options but in all cases, I am getting #VALUE as result in all cases.

Note: I found some similar question but not addressing this specific case.



Read more here: https://stackoverflow.com/questions/67395695/sumproduct-returns-value-when-cell-has-an-empty-string

Content Attribution

This content was originally published by David Leal at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: