1

I have data in two columns. I would like to sum the two columns (A and B) based on the max value in each row and a condition (c) indicating if the row should be included in the sum. Example:

A    B    C
1    4    No
2    3    Yes
3    2    Yes
2    1    Yes
1    0    Yes

The result would be 3+3+2+1=9. Data is the first row should not be included because the condition in column C states "No".

I have been able to sum the values using the arrayformula below but have not been able to figure out how to add the condition.

=ARRAYFORMULA(SUM(IF(A1:A>B1:B,A1:A,B1:B)))

I am using Google Spreadsheets.

1

In your particular example, you can use a SUMPRODUCT formula.

=SUMPRODUCT((A1:A5*(A1:A5>=B1:B5)*(C1:C5="Yes"))+(B1:B5*(B1:B5>A1:A5)*(C1:C5="Yes")))

This approach may be clumsy in your real world example, though. If your real data is more complicated, post a snippet that shows that complication and I can take a more advanced crack at it.

  • 1
    This works nicely except it does not handle equal values in the same row. They are ignored. Adding the ">" appears to work great.(A1:A5>=B1:B5) – Scott Cravens Jan 20 '16 at 18:33
  • @ScottCravens That's a good point and I missed it. I updated the formula so future reader's won't have to catch the comments to get it to work for them. – Engineer Toast Jan 20 '16 at 21:14
0

I'm sure there is a one-cell solution, but you can just put the following formula in a helper column:

=IF(C1="No",0,IF(A1>B1,A1,B1))

If you copy that formula all the way down col D, the values can be summed to arrive at the value you are looking for.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy