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.
- values = 6, 10 and 12 (sum = 28)
- 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