Sum of rows in one column based on values in adjacent column

Question

I need to sum a range (D20:D22) but exclude any values in an adjacent column (G20:G22) where the Column G values is <-2. The resulting value is the divisor in a second calculation.

For example:

• D20:D22
  • values = 6, 10 and 12 (sum = 28)
• G20:G22
  • values = 1.87. 3.52 and 4.84
• G20 is less than 2, so D20 is excluded from the sum
• Expected sum: D20:D22 = 22 (10 + 12)

---

• Cell I20 = 8.36
• I20 divided by adjusted sum of D20:D22
  • = 0.38 (8.36/22)

---

I think that sumifs is probably part of the solution, but I can't work out how to write the formula.

See my spreadsheet. The formula is on sheet "BOD".

Hope this isn't too confusing. Thanks

Answer 1

Try this:

=I20/sumifs(D20:D22,G20:G22,">2")

Sumifs documehtation