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.
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 |