Help with forumla to work out sum of overdue invoices

[George Holmer]

Hi,

I have a spreadsheet listing all my invoices, paid and unpaid. I would like Excel to work out how much my overdue ones are worth based on the number of days overdue.

So, in column C I have invoice dates, in column G I have invoice amounts and in column I have paid amounts. That means that for an unpaid invoice, the corresponding cell in column I is empty. I want to show how the total outstanding amount for all invoices that are older than 30 days, older than 60 days and older than 90 days. If at all possible, I would like them not to include each other, i.e. display all invoices 31-60 days, all 61-90 days and all 90+ days.

Any ideas?

[zbor]

Upload example workbook.
make sure to avoid all private informations.

[George Holmer]

Thank you for your reply, I made an example file.

[Ace_XL]

31-60:
=sumproduct(--($i$2:$i$25=""),--(today()-$c$2:$c$25>30),--(today()-$c$2:$c$25<=60),$g$2:$g$25)

61-90:
=sumproduct(--($i$2:$i$25=""),--(today()-$c$2:$c$25>60),--(today()-$c$2:$c$25<=90),$g$2:$g$25)

90+:
=sumproduct(--($i$2:$i$25=""),--(today()-$c$2:$c$25>90),$g$2:$g$25)

[zbor]

Since you already have all in your table I would suggest you to add one column into existing table.. Something like this:

[George Holmer]

Thanks guys, that works brilliantly, both ideas are great.