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.


I am using Google Spreadsheets.


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


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

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


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