CODEWITHSUNDEEP
dategoogle-sheetsgoogle-sheets-formula
BenCamp
BenCamp

Reputation: 17

How to make ">"TODAY()-7 not count future dates

I have a google spreadsheet with dates that are in the future as well as in the past and am trying to countifs all the dates that are within the last 7 days and NOT the dates in the future. That is where ">"TODAY()-7 comes in.

I have been trying to use =COUNTIFS(VALUES!A:A,">"&TODAY()-7,VALUES!B:B,"SCHEDULED") but this counts the dates for the future as well. Would I add something that counts the dats inbetween two other values?

Here is a spreadsheet for this problem.

Upvotes: 1

Views: 292

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

Updated

The following formula will count the number of instances when column a is a in a range of including today and 7 days back AND column B corresponding value ="Scheduled". Note that the iferror will be necessary to account for a blank returned filter.

=if(ISERROR(FILTER(A:A,A:A<=today(),A:A>=Today()-7,B:B="SCHEDULED")),0,
counta(FILTER(A:A,A:A<=today(),A:A>=Today()-7,B:B="SCHEDULED")))

Since the above formula has a relative value (today()), one can test with this hardcoded formula to the below dataset:

=if(ISERROR(FILTER(A:A,A:A<=date(2022,12,15),A:A>=Today()-7,
B:B="SCHEDULED")),0,counta(FILTER(A:A,A:A<=date(2022,12,15),
A:A>=date(2022,12,15)-7,B:B="SCHEDULED")))
Column A Column B
12/4/2022 NOT
12/5/2022 SCHEDULED
12/6/2022 NOT
12/7/2022 SCHEDULED
12/8/2022 SCHEDULED
12/9/2022 NOT
12/10/2022 SCHEDULED
12/11/2022 NOT

Upvotes: 1

Related Questions