Forum Discussion
Counting overdue complaints based on days overdue
Hi Everyone,
I'm setting up a complaints tracker and want to be able to highlight overdue complaints which are still listed as open.
There is a target date for the complaint to be closed out and I want to be able to see any "Open" complaints which are over the due date.
I've set up a cell which will automatically add todays date and managed to calculate a formula which will count the number of dates between both dates but this won't count if the complaint is "open" or "closed" and I haven't found a way to carry this sum down, I have to manually enter it.
Hoping someone can lend a hand on this.
Thanks,
Jade
5 Replies
I've attached a photo of the tracker for reference.
Thanks
JadeYou can probably use the COUNTIFS function. See Excel COUNTIFS Function
If you want more specific help, please provide information about the setup of your worksheet, or attach a sample workbook.
HansVogelaar Thank you for your speedy reply. I think a Count IF function would be best, do you think I would still need to count the overdue dates separately.
I have attached a sample sheet for reference.Does this do what you want? It's an array formula, you have to confirm it with Ctrl+Shift+Enter, otherwise it won't work as intended.
=COUNTIFS(A4:A33,">"&I4:I33,H4:H33,"Open")
