 # Need help with SUMIF(S) formulas. (I think)

I have 11 different sets of numbers(debts) with a total budget of \$11000. I would like to subtract \$1000 from each specific debt. If said debt is less than \$1000 then subtract remaining balance. I would then like to take the remaining balance of budget and apply evenly to remaining debts (unless debt is less than \$1000 in which case it would default to subtract remaining balance). Basically snowballing \$11000 across the 11 debts until they are all paid down.

5 Replies

# Re: Need help with SUMIF(S) formulas. (I think)

Could you please share a screen or a file containing a sample of your current table? I have some ideas about possible solutions, but want to make sure about what do you really need.

# Re: Need help with SUMIF(S) formulas. (I think)

Let's say the 11 amounts are in A2:A12.

To calculate the amounts to subtract, select B2:B12 and enter the formula

=IF(A2:A12<1000,A2:A12,(11000-SUMIF(\$A\$2:\$A\$12,"<=1000"))/COUNTIF(\$A\$2:\$A\$12,">1000"))

If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

To calculate the remaining amounts, select C2:C12 and enter the formula

=A2:A12-B2:B12

Again, if you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.

# Re: Need help with SUMIF(S) formulas. (I think)

Thank you for helping. I tried your formula, unfortunately, it did not work. Top and bottom cells had #SPILL and the in between cells did not do any math.

# Re: Need help with SUMIF(S) formulas. (I think)

 1 2 3 4 5 6 7 8 9 10 \$308.59 -\$691.41 I want to take the overage (1000-308.59) in Column 1 and apply to below value and so on (if starting value is below \$1000) \$858.59 -\$141.41 \$979.25 -\$20.75 \$1,013.06 \$13.06 -\$986.94 \$2,380.38 \$1,380.38 \$380.38 For values over 1000 I want it to subtract 1000 plus whatever overages from above cells \$2,784.99 \$1,784.99 \$784.99 \$3,115.89 \$2,115.89 \$115.89 \$5,333.76 \$4,333.76 \$2,333.76 \$6,035.61 \$5,035.61 \$3,035.61 \$9,158.98 \$8,158.98 \$7,158.98 \$15,623.93 \$14,623.93 \$13,623.93

Demo workbook: