1

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?!

2
  • So, let me get this straight: A is the sum of B and C, and you want to sum the values in A where B or (inclusive) C is not blank. What is the value of A when B and C are both blank? Is it not 0? If that's true, you should need no condition to sum the values in A.
    – Excellll
    Jul 23, 2014 at 14:11
  • Sadly not -- this is messy data that's been completed through an online survey. Columns B and C are female/male split. So they might know that 10 people were counted, but not know the gender divide. But if I sum column A and then start doing sums with that total, it'll skew things.
    – Mat
    Jul 23, 2014 at 14:19

3 Answers 3

1

You could do something like this:

=SUMPRODUCT($A$2:$A$11*($B$2:$B$11&$C$2:$C$11<>""))

The idea is that the concatenation of B and C is blank only if both are blank.

1

Aha - I needed the SIGN function:

=SUMPRODUCT(A2:A10,SIGN((B2:B10<>"")+(C2:C10<>"")))

I still find it weird that, as far as SUMPRODUCT is concerned, TRUE + TRUE = 2. And that 2 doesn't just evaluate to TRUE when -- is in play.

2
  • The -- is used to convert the Boolean value to a numeric value. Think of it as TRUE*(-1)*(-1). The reason for this being done is that SUMPRODUCT only works on numerical arrays.
    – Excellll
    Jul 28, 2014 at 13:48
  • Look at the statement as it was written --((B2:B10<>"")+(C2:C10<>"")). To me, this should convert to Boolean as its last step; i.e., even if it sees a '2', it should just interpret this as TRUE. And TRUE*(-1)*(-1) just equals TRUE, not 2!
    – Mat
    Jul 29, 2014 at 9:32
0

I think you may consider =SUMIFS() function instead.

Your condition can be rewritten as:

  • Condition 1: Column B is filled in and Column C is blank; OR
  • Condition 2: Column B is blank and Column C is filled in; OR
  • Condition 3: Column B and Column C are both filled in.

Since the 3 conditions are mutually exclusive, the complicated SUMPRODUCT is essentially =SUMIF(Condition 1)+SUMIF(Condition 2)+SUMIF(Condition 3). Rewritten as SUMIFS:

=SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"")+SUMIFS(A2:A10,B2:B10,"",C2:C10,"<>")+SUMIFS(A2:A10,B2:B10,"<>",C2:C10,"<>")

Performance-wise, this solution may be better than using SUMPRODUCT as well if there is a large number of rows to summarise.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .