Need to add relative date to my COUNTIFS formula

[jhgeorge]

Hello All,
I have been a lurker for many years and 99% of the time I find my answer or a different way to solve my issue, but this one has stumped me.

I am tracking a multitude of items in Excel using COUNTIFS. I have my formula counting several different columns and reporting back to my table when a row meets the COUNTIFS criteria. However, I have been unsuccessful to add RELATIVE DATE to my formula. I want the formula to only report the last 14 days. I have been successful in adding DATE with a range, and can manually change the date each day, but that is 30 locations/formulas that have to be changed each day.

=COUNTIFS('WS Table'!$T:$T,List!$D$3,'WS Table'!$E:$E,[@Site],'WS Table'!$L:$L,List!$C$9,Student[Timestamp],"<="&TODAY(),Student[Timestamp],">="&TODAY()-14)...this is my most current attempt, after much research, but I get "#VALUE" in the cell.

Currently, successful formula...covers the entire month. I really want to use relative date...so I do not have to change the date each day.
=COUNTIFS(CovidTwo[Site/Dept],A2,CTwo[Type],List!$C$3,CTwo[K Status],List!$A$3,CovidTwo[Date Reported],">="&DATE(2020,11,1),CTwo[Date Reported],"<="&DATE(2020,11,30))

Thanks

 

[CeceR]

I suspect your desired formula is returning #VALUE because your ranges aren't the same. The first few criteria refer to full columns ('WS Table'!$T:$T), but the ranges you are evaluating for the date criteria are table columns. Try changing the column references to table fields like you have them in the formula that is working currently.