Hey all

I'm having a problem adding extra conditions to a formula.

I have two formulas in the table (attached) that work out the how long an unpaid invoice is overdue, and how much it is overdue by (in columns F and G respectively). Once an invoice has been paid in full, the data in these cells disappear.

What I need to do is figure out a way to calculate these things where there may be incremental payments.

The formula for the amount overdue is simple enough at the moment:

=IF(E3<C3,C3-E3,"")

But, I wanted to add something that says, if the Date of Invoice cell in the rows below is blank AND the sum of the rows in the below columns with blank date cells is less than the total invoice amount, then subtract the sum of the below cells from the invoice total. I was thinking of something like

=IF(E3<C3,C3-E3,"",IF(AND(B4="",(E3+E4)<C3,C3-(E3+E4))))

But that's just returning an error. Also, this only looks at the row below, whereas, I would need to be looking at maybe the ten rows below in case the invoice was paid in a number of increments.

I also need to do something similar for the days overdue column - and the formula in that is a bit more complicated (although I'm sure I put it together in a fairly convoluted way!!!)

I'm really just feeling my way along here - and advice would be really appreciated - I can't figure this out and it's sending me cross-eyed!!

Thanks!

Sample increments table.xls