CODEWITHSUNDEEP
excelexcel-formula
David Leal
David Leal

Reputation: 6769

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.

UPDATE: I realized working on my real situation, the issue was not the blanks, but rather trying to use SUMPRODUCT function when one argument is on row format and the other one in column format, for some reason it doesn't work, transposing the second argument using TRANSPOSE function works, I don't know why. The following picture shows the situation enter image description here

Upvotes: 0

Views: 1339

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

I am using O365 and your first formula works for me for conditional blanks.

=SUMPRODUCT(A1:A2, B1:B2)

for test purpose, B1 has =IF(C1<>"",1,""). See below snapshot.

enter image description here

You can use an IF formula inside SUMPRODUCT like below to get around the issue.

=SUMPRODUCT(A1:A2, IF(B1:B2<>"",B1:B2,0))

Edit:

SUMPRODUCT is an array formula and it requires the arrays involved in the operation of the same dimension & size.

So in principle, when we multiply two columns the data array is (note semicolon between entries):

{A1;A2;A3;A4;....An}

whereas when it is spread across a row then it is (note comma between entries):

{A1;B1;C1;D1;E1....}

So when you try to implement it for data spread in rows with columnar data, the array size criteria is fulfilled but the dimension criteria is not and therefore it returns an error! Applying TRANSPOSE changes the array to matching dimension and therefore returns result on expected lines.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37125

You can omit 3rd array of your formula. Try-

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

Upvotes: 1

Related Questions