Dec 16 2020 05:14 AM
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
Dec 16 2020 05:21 AM
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.
Dec 16 2020 05:23 AM
I've attached a photo of the tracker for reference.
Thanks
Jade
Dec 16 2020 05:26 AM
@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.
Dec 16 2020 05:46 AM
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")
Dec 16 2020 06:08 AM
@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