COUNTIFS Date Cell Reference +-Days

FanofExcel18

Board Regular
Joined
Jun 7, 2018
Messages
59
Trying to figure out a Countifs formula to provide Something that is greater than 30 days but less than 90. Using a cell reference for the date.

Example: A1 = 1/6/2020, trying to figure out if its greater than 30 days
I've tried but can't figure it out:
=COUNTIFS(Sheet2!B:B,D2,Sheet2!C:C,">="&$A$1+30,Sheet2!C:C,"<"&$A$1+90)
=COUNTIFS(Sheet2!B:B,D2,Sheet2!C:C,">="&($A$1+30),Sheet2!C:C,"<"&($A$1+90))

Table:

1/6/2020​
PersonDateNameCount
John Smith1/4/2020 14:27John Smith
0​
John Smith1/4/2020 11:27Jane Doe
0​
Jane Doe1/4/2020 4:01
Jane Doe1/3/2020 20:02
Jane Doe11/20/2019 15:18
Jane Doe11/20/2019 13:10
John Smith11/20/2019 11:01
Jane Doe11/20/2019 10:02
Jane Doe11/20/2019 8:58
John Smith11/20/2019 8:40
Jane Doe11/20/2019 8:20
Jane Doe11/19/2019 23:01
John Smith11/19/2019 16:19
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
That formula works for me, are you sure that your dates are real dates & not text?
 

FanofExcel18

Board Regular
Joined
Jun 7, 2018
Messages
59
I figured it out....I had reverse my Less than Equal to and change "+" to - ...appreciate the review
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,617
Office Version
365
Platform
Windows
Glad you sorted it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,481
Messages
5,444,739
Members
405,299
Latest member
rcurtin

This Week's Hot Topics

Top