Counting overdue complaints based on days overdue

%3CLINGO-SUB%20id%3D%22lingo-sub-1993395%22%20slang%3D%22en-US%22%3ECounting%20overdue%20complaints%20based%20on%20days%20overdue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993395%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20setting%20up%20a%20complaints%20tracker%20and%20want%20to%20be%20able%20to%20highlight%20overdue%20complaints%20which%20are%20still%20listed%20as%20open.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThere%20is%20a%20target%20date%20for%20the%20complaint%20to%20be%20closed%20out%20and%20I%20want%20to%20be%20able%20to%20see%20any%20%22Open%22%20complaints%20which%20are%20over%20the%20due%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20set%20up%20a%20cell%20which%20will%20automatically%20add%20todays%20date%20and%20managed%20to%20calculate%20a%20formula%20which%20will%20count%20the%20number%20of%20dates%20between%20both%20dates%20but%20this%20won't%20count%20if%20the%20complaint%20is%20%22open%22%20or%20%22closed%22%20and%20I%20haven't%20found%20a%20way%20to%20carry%20this%20sum%20down%2C%20I%20have%20to%20manually%20enter%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHoping%20someone%20can%20lend%20a%20hand%20on%20this.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%2C%3C%2FP%3E%3CP%3EJade%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1993395%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993442%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20overdue%20complaints%20based%20on%20days%20overdue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993442%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F903764%22%20target%3D%22_blank%22%3E%40JadeHughes93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20probably%20use%20the%20COUNTIFS%20function.%20See%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-countifs-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EExcel%20COUNTIFS%20Function%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20more%20specific%20help%2C%20please%20provide%20information%20about%20the%20setup%20of%20your%20worksheet%2C%20or%20attach%20a%20sample%20workbook.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1993445%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20overdue%20complaints%20based%20on%20days%20overdue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1993445%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20attached%20a%20photo%20of%20the%20tracker%20for%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EJade%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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