Tricky conditional formatting and sumif function

[CuriousG]

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.