CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
onemoreissue
onemoreissue

Reputation: 13

Google Sheets: sum column based on conditions of other column

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

Upvotes: 0

Views: 91

Answers (1)

marikamitsos
marikamitsos

Reputation: 10573

You could try these formulas:

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

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

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

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

  1. 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

enter image description here

Functions used:

Upvotes: 1

Related Questions