Excel - How to calculate the sum of a different column values based on the conditional values of another column

0
votes

I am trying to calculate the PAR values, whose formula is:

PAR-3 = Total Outstanding Values (for 0-3 overdue days) / Gross outstanding balance = (43,100 + 1,08,000)/ 11,54,700 = 0.130

Similarly I shall calculate for PAR-7, PAR-15, PAR-30 etc

Now for the attached excel sheet, for calculating PAR-3, I need the sum of Outstanding amount values for the given range of overdue days. Only if the range of "Days Overdue" is between 0-3, then only select the corresponding cell of "Outstanding Amount" and finally sum it up.

I tried looking for solutions using VLOOKUP and HLOOKUP, Pivot Tables etc, but in vain.

My Excel Sheet

1
excelexcel-formula

1 Answers

0
votes

Use SUMIF() and SUMIFS() formulas.

0-3 days:

=SUMIF(F2:F29;"<4",O2:O29)

4-7 days:

=SUMIFS(O2:O29;F2:F29,">3",F2:F29,"<8")

8-15 days:

=SUMIFS(O2:O29;F2:F29,">7",F2:F29,"<16")

16-30 days:

=SUMIFS(O2:O29;F2:F29,">15",F2:F29,"<31")

30+ days:

=SUMIF(F2:F29;">30",O2:O29)