This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am trying to calculate the count of a string of data based upon the last 7 days and the last 30 days using the current date.
I do not want to manually update the date.
Column A = Dates
Column B = Data
I can make the count work by using the manual date:
=COUNTIFS(Data2!$A:$A,"<11/5/2019",Data2!$A:$A,">10/27/2019",Data2!$B:$B,"Hard Rock")
This brings back the correct counts.
But, If I use the TODAY() function (or the NOW() function), or direct the criteria to a cell that holds the TODAY() function, it does not work.
=COUNTIFS(Data2!$A:$A,"<=TODAY()+1",Data2!$A:$A,">=TODAY()-8",Data2!$B:$B,"Hard Rock")
=COUNTIFS(Data2!$A:$A,"<=E31",Data2!$A:$A,">=F31",Data2!$B:$B,"Hard Rock")
My theory is that the criteria section cannot hold formulas?
Can anyone help with my issue?
Thanks
~Kim
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
=COUNTIFS(Data2!$A:$A,"<=TODAY()+1",Data2!$A:$A,">=TODAY()-8",Data2!$B:$B,"Hard Rock")
Your "TODAY()+1" is a string, but you need to call the function:
=COUNTIFS(Data2!$A:$A,"<="&TODAY()+1,Data2!$A:$A,">="&TODAY()-8,Data2!$B:$B,"Hard Rock")
Andreas.
It works!
It also helps me to see how the function works as well.
Thank you so much for your help.
~Kim