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
 

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.
What format are your dates in Report!$G$1:$G$100000? Would you be able to share some sample data from that range please?
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top