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


Try this:


Sumifs documehtation


Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.