Google Sheets: sum column based on conditions of other column

Question

I would like to sum Total Value Column conditioned by Selection.

1. If Selection is Yes sum all yes's in Total Value
2. If Selection is No find highest No value in Total Value
3. Add Yes sum and No (single max value) together

Illustrated:

<table><tbody><tr><th>Selection</th><th>Total Value</th></tr><tr><td>No</td><td>17.80</td></tr><tr><td>Yes</td><td>5.00</td></tr><tr><td>Yes</td><td>2.50</td></tr><tr><td>Yes</td><td>2.50</td></tr><tr><td>Yes</td><td>5.00</td></tr><tr><td>Yes</td><td>5.00</td></tr><tr><td>Yes</td><td>5.00</td></tr><tr><td>Yes</td><td>1.50</td></tr><tr><td>No</td><td>16.60</td></tr></tbody></table>

1. Yes's sum = 26.50
2. No = 17.80
3. Total Value = 44.30

solution needs to be native google sheet formula, i.e. sumifs(), if(), etc.

Thank You.

table of date

Answer 1

You could try these formulas:

1. If Selection is Yes sum all yes's in Total Value

=SUMIF(O2:P10,"=Yes",P2:P10)

2. If Selection is No find highest No value in Total Value

=MAX(ArrayFormula(IF(O2:O10="No",P2:P10)))

3. Add Yes sum and No (single max value) together

=SUMIF(O2:P10,"=Yes",P2:P10)+
MAX(ArrayFormula(IF(O2:O10="No",P2:P10)))

Or just =Q2+Q3

Functions used:

• SUMIF
• ArrayFormula
• MAX
• IF