Countif with dates compared to two cells Sheets

[srob93]

Hello I am really hoping someone on here can help, I am a bit of an excel/gsheet newbie...

I am trying to create a TOTAL of actions overdue depending on the date. I have managed to use the COUNTIF function to count the dates that are overdue in column H.

The next step is where I am stuck... is that I need the item to be taken off the total once the item is completed in column I. See attached. E.G. - Now cell H4 has been completed in cell H5 I need cell H37 to drop back down to 5 overdue.

I have tried a few formulas so far but this is the closest I have got...

Is there an extension to the formula I can use? Or perhaps another easier way of achieving this?

Thank you in advance!!

 

[Fluff]

Hi & welcome to MrExcel.

You can use the COUNTIFS function for what you need.

 

[srob93]

Hi & welcome to MrExcel.

You can use the COUNTIFS function for what you need.

Thank you for your reply.

I have started to use the COUNTIFS but not sure how to end the formula to get what I need ... do you have any suggestions?

 

[Fluff]

What is your current formula?

 

[srob93]

What is your current formula?

Sorry if you cannot see it on the attached image.

I have got this far... =COUNTIFS(H4:H36, "<="&TODAY())

but need a secondary section to the formula to connect it to the dates incolumn I.

what do you think? Have uploaded the image again for reference.

 

[Fluff]

How about

=COUNTIFS(H4:H36, "<="&TODAY(),I4:I36,"")

 

[srob93]

That works!!!

I am so happy I could cry - have been trying to figure that out forever ? thank you so much!

??????

 

[Fluff]

You're welcome & thanks for the feedback.