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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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, 2016 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, 2016 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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.