Countifs Between Date Ranges

[kylehawkins79]

Hello,

I am trying to put together a formula to calculate the number of days action items are past due. Im reading the dates (Column H) between 61 and 90 days past due per se, if the completed column (Column I) is blank and if its designated as an action (Column D). Formula is as follows =COUNTIFS(Findings!$H$3:$H$347,"<="&TODAY()-90,Findings!$H$3:$H$347,">="&TODAY()-61,Findings!$I$3:$I$347,"",Findings!$D$3:$D$347,"Action")

It doesnt seem to be working. any help would be appreciated. Thank you

 

[Special-K99]

I don't think this will solve it but put brackets around the TODAY() calculation, ie

=COUNTIFS(Findings!$H$3:$H$347,"<="&(TODAY()-90),Findings!$H$3:$H$347,">="&(TODAY()-61),Findings!$I$3:$I$347,"",Findings!$D$3:$D$347,"Action")

Apart from that it looks ok to me.

If that still doesn't work supply some example data where you don;t it's producing the correct results.
Ultimately isolate each part of the formula and check the value, e.g. let's say D37 looks like it has the word Action in the cell. In a blank cell enter =(D37="Action") Result is FALSE even though it looks like it contains the word "Action". Testing on =LEN(D37) produces 7 characters, should be 6, there's a space at the end of D37 so it's not "Action" it's "Action ", that's why it's failing, do something along those lines.

 

[Fluff]

Another thing to check, if you change the format of col H to "General" do you see numbers like 43525, or do you still see dates?

 

[kylehawkins79]

Thank you for the help. I tried the brackets around today and that didnt work. Also they are all general as you mentioned. I know its close because the first one in the series works =COUNTIFS(Findings!$H$3:$H$348,"<="&TODAY()-91,Findings!$I$3:$I$348,"",Findings!$D$3:$D$348,"Action") Here its just looking for anything over 91+ of course. Its when I try and add the between is when it fails

 

[Fluff]

Based on today's date you are looking for a cell that is <= 1st March & at the same time >= 31st March.
This will never be true so your formula will always return 0

 

[kylehawkins79]

how would i represent it looking at how many records are between those dates in that formula?

 

[Fluff]

To find dates between -90 & -61 simply swap the > & < around