Need to add relative date to my COUNTIFS formula

jhgeorge

New Member
Joined
Nov 29, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

CeceR

New Member
Joined
Mar 30, 2011
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,251
Messages
5,600,541
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top