CountIF Statement with multiple criteria using the Today() Function

Hello,

I am building a dashboard off off the data contained within my Issue / Action tracker. What I need to do is develop a formula that will allow me to returns a total count of items for 2 areas. Past Due and Upcoming date. I have calculated columns for Status, Formula is =IF(ISBLANK(A15),"",IF(ISBLANK(I15),"Ongoing",IF(ISBLANK(J15),"Open","Closed"))) which will determine if the Item is Open or Closed
and a Manually entered filed for Due Date.

I am having an issue with the following formula. It works with the Today() Function, but errors out if I use a <=Today() for Past due and when I use the Today()+10 for "Upcoming Tasks" it does not return the correct data

The formula I have for Past Due is this =COUNTIFS($K$8:$K$1497,"Open",$I$8:$I$1497,<=TODAY()) and the one I am using for "Upcoming is =COUNTIFS($K$8:$K$1497,"Open",$I$8:$I$1497,TODAY()+10)

Any suggestions or assistance would be greatly appreciated.

Attached is a sample of the file. Please be aware that the formula for past due does NOT contain the <= sign as Excel will not allow me to save it, Since I am doing something incorrectly!

Thank you in advance,
Tim

Sambo kid,

Thank you for the quick reply.

The formula appears to work, however it does return the wrong value for the total items. It returns a count of 13 and there are only 9 in my file. =COUNTIFS($K$8:$K$1497,"Open",$I$8:$I$1497,"<="&TODAY()+10)

The formula returns the correct count for Past Due (4) - =COUNTIFS($K$8:$K$1497,"Open",$I$8:$I$1497,"<="&TODAY())

I am wondering if the "Upcoming Tasks" formula is double counting the Past Due since both formulas have the common syntax of be double counting the date for Today since it reads "<="&TODAY(). I tried testing this by eliminating the < the sign, but then it returns a 0

Thoughts?

Sure Thing. I have attached the file. Again, thank you for your assistance.

Tim

Originally Posted by Sambo kid

I used the two formulas you have in post #3 and repointed them at columns D and F and they returned 4 for past due and 9 for upcoming.

this returns 4 =COUNTIFS($F$8:$F$1497,"Open",$D$8:$D$1497,"<="&TODAY())
this returns 9 =COUNTIFS($F$8:$F$1497,"Open",$D$8:$D$1497,"<="&TODAY()+10)

I tried it with an IF / THEN statement on a line by line basis and get the same results. I used this in cell J8 and down... =IF(AND(F8="open",D8<=$I$7),"yes","no")
And it is based on this in I7 =TODAY()+10

Correct those are the same values that I receive. However, if you look at the file, you will see that the results should in fact be: 4 for Past Due and 5 for Upcoming Tasks not 9

4 for Past Due and the Formula is =COUNTIFS($F$8:$F$1497,"Open",$D$8:$D$1497,"<="&TODAY()) THIS IS CORRECT

I also receive 9 for "Upcoming Tasks" with the formula of =COUNTIFS($F$8:$F$1497,"Open",$D$8:$D$1497,"<="&TODAY()+10) This is INCORRECT

I am wondering if it is double counting the Items that are Past Due. Thoughts?

Thank you

Thank you, that last formula works perfectly. I appreciate your responsiveness and willingness to help out. Thank you again

Tim