# Countif dates 0/1/2/3 and beyond weeks ago

#### Zedrick13

##### Board Regular
Hi everyone,

I have a table that has column A (IRR!A:A) with dates I want to use countif to count the number of days that fall in the current week, previous week, 2 weeks ago, and 3 and more weeks ago. I already figured out how to count for the current week using this formula: =COUNTIF(IRR!A:A,TODAY()+1-WEEKDAY(TODAY()-1)). Can you please help me with the formula for the previous week, 2 weeks ago, and 3 and more weeks ago? Thanks in advance.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

##### Well-known Member
Hi Zedrick13,

Does this do what you ask?

Zedrick13.xlsx
ABCDEF
120-Nov-20Weeks:123
223-Nov-20Count:259
324-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D2:F2D2=COUNTIFS(\$A:\$A,">"&TODAY()-D\$1*7,\$A:\$A,"<="&TODAY())

#### Zedrick13

##### Board Regular
=COUNTIFS(\$A:\$A,">"&TODAY()-D\$1*7,\$A:\$A,"<="&TODAY())
This looks great, @Toadstool. Can I just replace "D\$1" for the number of weeks then? By the way, I need the formula to start looking from a Monday to Sunday per week.

#### Zedrick13

##### Board Regular
Hi Zedrick13,

Does this do what you ask?

Zedrick13.xlsx
ABCDEF
120-Nov-20Weeks:123
223-Nov-20Count:259
324-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D2:F2D2=COUNTIFS(\$A:\$A,">"&TODAY()-D\$1*7,\$A:\$A,"<="&TODAY())
By the way, looking into the formula again it doesn't seem to add up properly. Manually counting the dates, don't you think the result should be like this:

Monday as the start of the week (preferred)
0 week (21-Dec to 27-Dec) = 1
1 week ago (14-Dec to 20-Dec) = 2
2 weeks ago (7-Dec to 13-Dec) = 3
3 weeks and more ago (earlier dates to 6-Dec) = 8

Sunday as the start of the week
0 week (20-Dec to 26-Dec) = 1
1 week ago (13-Dec to 19-Dec) = 2
2 weeks ago (6-Dec to 12-Dec) = 2
3 weeks and more ago (earlier dates to 5-Dec) = 8

##### Well-known Member

I misunderstood. I'll put the date ranges as two rows to make checking and the formula simpler.

Zedrick13.xlsx
ABCDEFG
120-Nov-20Start:21-Dec-2014-Dec-2007-Dec-2030-Nov-20
230-Nov-20End:27-Dec-2020-Dec-2013-Dec-2006-Dec-20
330-Nov-20Weeks:0123
427-Nov-20Count:1236
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D1:G1D1=TODAY()-(WEEKDAY(TODAY(),2)*(D\$3*7))
D2:G2D2=D1+6
D4:G4D4=COUNTIFS(\$A:\$A,">="&D\$1,\$A:\$A,"<="&D\$2)

#### Zedrick13

##### Board Regular
I misunderstood. I'll put the date ranges as two rows to make checking and the formula simpler.

Zedrick13.xlsx
ABCDEFG
120-Nov-20Start:21-Dec-2014-Dec-2007-Dec-2030-Nov-20
230-Nov-20End:27-Dec-2020-Dec-2013-Dec-2006-Dec-20
330-Nov-20Weeks:0123
427-Nov-20Count:1236
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D1:G1D1=TODAY()-(WEEKDAY(TODAY(),2)*(D\$3*7))
D2:G2D2=D1+6
D4:G4D4=COUNTIFS(\$A:\$A,">="&D\$1,\$A:\$A,"<="&D\$2)
I really appreciate your help. But, is there a way to do this without referencing the dates? I need the formulas to automatically adjust based on the current date. For example, on the 4th of January, I need the formula to automatically recognize that the 27th would be for the past week. If we are to reference the date ranges, I would then need to always update them. Sorry if I'm asking for too much. Just wanna know if this is possible.

##### Well-known Member

You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
330-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D2:G2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7))+6)

#### Zedrick13

##### Board Regular
You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
330-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D2:G2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7))+6)
Something is still strange in the formula and the results you showed for some reason. Looking at column A, the result should be something like the below, right?

0 week - 1
1 week - 2
2 weeks - 3
3 or more weeks - 8

Or, was there something I missed?

#### Zedrick13

##### Board Regular
You did not enter the date ranges; they were calculated based on today.

If you'd rather just have a formula then does this work?

Zedrick13.xlsx
ABCDEFG
120-Nov-20Weeks:0123
230-Nov-20Count:1236
330-Nov-20
427-Nov-20
530-Nov-20
601-Dec-20
701-Dec-20
804-Dec-20
907-Dec-20
1010-Dec-20
1113-Dec-20
1214-Dec-20
1320-Dec-20
1427-Dec-20
IRR
Cell Formulas
RangeFormula
D2:G2D2=COUNTIFS(\$A:\$A,">="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7)),\$A:\$A,"<="&TODAY()-(WEEKDAY(TODAY(),2)*(D\$1*7))+6)
Also, I'm getting a different result when replicating what you did for some reason. Please see attached screenshot.

#### Attachments

• Countif dates 0,1,2 or 3 and beyond weeks ago.png
20.2 KB · Views: 3

#### Zedrick13

##### Board Regular
Also, I'm getting a different result when replicating what you did for some reason. Please see attached screenshot.
I think I know why now. The return type must be 3 instead of 2.

Replies
1
Views
63
Replies
3
Views
77
Replies
6
Views
51
Replies
2
Views
76
Replies
1
Views
49

1,127,342
Messages
5,624,114
Members
416,012
Latest member
rockermom59

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

### Which adblocker are you using?

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

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