I have three columns of data - a sum column (A
) and then the two columns that form the sum (B
and C
). Sometimes, just one of the B
and C
columns is filled in; occasionally the B
and C
columns are both blank.
I want to SUM column A
(i.e., a total of the totals), only when there is data in columns B
and/or C
. So, I'd hoped that the following would work:
=SUMPRODUCT(--((B2:B10<>"")+(C2:C20<>"")),A2:A10)
But the problem is, if there is data in BOTH columns B and C the array formed from (B2:B10<>"")+(C2:C10<>"")
will sum to 2, and the preceding --
doesn't convert it to a TRUE
(e.g., {1,1,0,1,0,1,1,1,0} + {1,0,1,1,0,1,1,0,1} = {2,1,1,2,0,2,2,1,1}
) and that final array is then multiplied with the values in column A
, thus doubling values 1, 4, 6 and 7.
This seems like odd behaviour! How do I make it behave properly?!
A
is the sum ofB
andC
, and you want to sum the values inA
whereB
or (inclusive)C
is not blank. What is the value ofA
whenB
andC
are both blank? Is it not 0? If that's true, you should need no condition to sum the values inA
.