Formula help - Is this possible?

amrich88

New Member
Joined
Feb 27, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
Context:
I have a budget I am building and this sheet I am working on is for tracking payments for business debt. How this sheet works is I put all of the card information at the top and down below is a month by month view of what the minimum payment and any extra amount does to impact the balance in that month. So, "G2" has a current balance that reflects in "D27". Anything entered in C27 & B27 will subtract from D27(Balance). B43 is the total amount allotted to go towards an extra payment for the given month (Month 1) and B44 is the amount remaining when a value is allotted to the 'Extra' column. When a balance gets "Zeroed out" I put a zero under "New Min". That new min (which pulls from F2:17) will 'TRIM' and take the value from E27:E42 rather than F2:F17 - I use [=IF(TRIM(E27)= "",F2,E27)] for all Payment columns so that I can zero out the minimum payments for any month and any card so that there isnt a credit balance going forward from when it gets paid off.

Now my question:
I want C44 to SUM when there is a zero in E27:E42. But only where the zero maps to the corresponding minimum payment because I will be snowballing the minimum payments that get paid off. It would look like the attached (second screenshot). Only I need them to only sum where there are zeros and to pull from the corresponding cell. For example, in Screenshot 2, The Balance in Month 1 for cells "D27; "D29"; "D33" are all paid off so I enter zeros in the New Min column for "I27; I29"; "I33" and that sets the "Payment" Column in those rows to zero from "$19.66"; "$10.97"; "$35". Because of this condition, I need/want G44 to give me an aggregate total in one cell where zeros exists in New Min, return a value from minimum payments. I27=D2; I29=D4; I33=D8.

I need them to map 1:1; so D2-->17 = E27-->42; I27-->42 etc etc. and to aggregate to C44; G44 etc etc. This automates the sum of minimum payments to the allotted extra.

I hope that makes sense...

[TO LONG; DIDNT READ]


There is a zero in I27, so put $29 from D2 into cell G44
There is a zero in I29, so put $35 from D4 into cell G44 and add it to the $29
There is a zero in I33, so put $35 from D8 into cell G44 and add it to the $29 & $35 payments to equal $99; which will cause F44 to add F43 and G44 together to equal a new allotted total to pay extra towards a debt.

The condition would exist for the whole Range of D2:17 (The source) when zeros exist in multiple ranges starting with E27:E42; I27:I42; Etc..
 

Attachments

  • 2022-02-27 17_23_03-Window.png
    2022-02-27 17_23_03-Window.png
    59 KB · Views: 20
  • Screenshot 2.png
    Screenshot 2.png
    60.7 KB · Views: 19

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top