I have a table of invoices. If a new invoice is added and that customer already has X invoices within the last 90 days that are not recorded as "Paid" in the status column then I want the new invoice entry to be formatted with a red background.

I also want to maintain a running total of the value of all invoices whose status is "Paid", "Pending" etc.

I want to achieve this by using formulas and not vba if at all possible.

The attached spreadsheet should fill in any gaps in my explanation.

Attached Files

Testing formulas.xlsx (12.5 KB, 7 views)

Here you go!

Edit
About totals: When you set any filter to data table, the totals are calculated for visible rows only (i.e. they work like subtotal)!

Attached Files

Testing formulas.xlsx (20.4 KB, 8 views)

WOW that was quick I'm very grateful