QUESTION :
I have a row of cells with 12 Monthly columns, G4 (January) through R4 (December), in which I will input the amount paid towards a monthly billing. [Note that an early comment discusses multiple years in multiple rows. However, that is no longer the case; it is just a single year, in row 4.]
When the bill has an amount due, it is a standard amount, the value of which is stored in cell F4.
If a month does not have an amount due, I leave the associated G4:R4 cell blank. If an amount is due, I enter the amount paid (zero if nothing), in the associated cell.
I would like for cell A4 to show a running total of the outstanding balance (amounts due minus amounts paid). Example: Monthly bill is 100.00 (F4). I paid 75 dollars in January (G4), so the running total I still owe is 25.00 (A4). I paid 50 dollars in February towards February’s bill (H4), so now I owe 75 (A4).
Note that there will never be a case where there is no billed amount but I pay toward a prior balance; if there is a prior balance, there will always be a billing for the standard amount. So, a blank cell in G4:R4 means that nothing was billed; a value in G4:R4 means the standard amount was billed and the entered amount was paid.
ANSWER :
In A4 simply put:
=$F4*COUNT($G4:$R4)-SUM($G4:$R4)
EDIT:
after your comment removed some “$” signs so that the formula can be easily copied over the 15 needed rows.
In the comments you gave that the range of cells for your months is G4:R4
Now for your answer:
The formula you will want to put into your running total (A4) is:
=IF( SUM($G4:$R4) < $F$4*COUNTA($G4:$R4), $F$4*COUNTA($G4:$R4) – SUM($G4:$R4), 0)
Lets break this down and analyze the parts:
COUNTA($G4:$R4)
COUNTA is short for “Count All”. It counts the number of non-blank cells within the range G4:R4. This is because you said the formula must ignore blanks. Furthermore, this keep track of how many months have ‘gone by’.
F4*COUNTA($G4:$R4)
This is the total amount owed. (Monthly payment)*(# of months)
SUM($G4:$R4)
This is the total amount paid. By default, this ignores the blanks.
So together we basically have
IF( TotalPaid < TotalOwed ) display TotalOwed – TotalPaid
Else display 0
Note: This answer was written prior to clarification of the question and does not reflect the current wording. I am leaving it in place only because the comments document the reasons for revisions to the question.
There are several answers that make the assumption that you pay something every month. What if you don’t? Without getting into issues like penalties or late fees, the billed amount is due each month regardless of the payment history. Counting months with payments doesn’t guarantee a correct answer. To do that, you need a method of specifying how many bills there have been.
There might be peculiarities in your billing cycle and the reference period could be in a previous year. If calculating the number of billing cycles to date is not straightforward, that calculation could be another Super User question. To keep it simple for this example, say January is billing period 1 and you aren’t concerned with the day of the month the bill is due.
For this simple example, A4 could contain:
=$F$4*MONTH(TODAY())-SUM(G4:R4)
I assume you are copying A4 to A5 for the next year, when you are using values in G5:R5. Some notes:
- If the standard billing amount can change from year to year, don’t anchore F4 with dollar signs; each row would have a relevant F column value.
- If the standard billing amount can change mid-year, you will need a more complicated formula.
- This formula assumes that everything balances in December and nothing carries over to the next year. If that is not the case, you would want to add the year-end balance in a cell, a logical place would be the start of the next year, and include that in your sum.