Countifs where date is less than

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11
Hi

I'm struggling with getting a formula that will do the above to work.

i was actually trying to obtain a count where a date is older than 8 years ago but could not get that to work. So I then tried entering that formula (=DATE(YEAR(TODAY())-8,MONTH(TODAY()),DAY(TODAY()))) in to it's own cell (Y1) and then referring to that n my formula; however I still cannot get this to work.

My formula now is =SUM(COUNTIF(Report!$G$1:$G$100000,"<"&Summary!Y1)) but the result returned is 0 when I know that there are dates within the column that are older than 8 years ago. I've tried amending Y1 to be an actual date but to no avail.
I've also tried amending the date to one that I can see actually exists in the column of dates; and have found that "=" works. But I can't get either "<" or ">" to work.

Could you advise where I may be going wrong?

Thanks
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

william_man

New Member
Joined
Oct 31, 2019
Messages
19
What format are your dates in Report!$G$1:$G$100000? Would you be able to share some sample data from that range please?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Just FYI, you don't need SUM there as you only have one criterion, so it's just:

=COUNTIF(Report!$G$1:$G$100000,"<"&Summary!Y1)
 

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11
Thanks

The dates are in (long) date format. I'm not sure How i can give you sample data other than copying it in to this reply (which then just comes out as unformatted text) - but it is just a column of dates that I am looking to run the formula on.
 

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11
Thanks for the reply.
The formula is actually a number of nested countifs - but I have continued removing these, and reverted to just countif in order to try and understand why the formula won't work (to no avail).
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I suspect your dates are actually text, not true dates. If you enter:

=isnumber(L2)

in a cell (assuming L2 contains one of your dates), does it return true or false?
 

stevemc2

New Member
Joined
Mar 1, 2013
Messages
11
That looks to be the case - it returns false.

Thanks for your reply - and for others.
It ended up unnecessary as I have been required to match a process elsewhere that is not actually using anything to generate the actual date 8 years ago but is going to be just an approximation (i.e. 2952 days ago) - so I will be matchhing that in my formula.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,056
Messages
5,472,205
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top