Forum Discussion
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 wo...
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.
- 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.
Demo workbook:
