Counting overdue complaints based on days overdue

Copper Contributor

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

@JadeHughes93 

You 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.

I've attached a photo of the tracker for reference. 


Thanks 


Jade

@Hans Vogelaar 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.

 

@JadeHughes93 

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")

@Hans Vogelaar Thank you very much for your help on this, I don't have much experience with Assay functions and this worked perfectly. I will save this in the future to use for other formulas. 

Thanks

Jade