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.

|improve this answer|||||
  • 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

|improve this answer|||||

Your Answer

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