• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countifs between dates

paulcherianc

paulcherianc

Member
Dear Experts!

Can you please check the formula given below. I am not getting results.

=COUNTIFS('NEW REGISTER'!$I$6:$I$5001,"<=" &"31/1/2014",'NEW REGISTER'!$I$6:$I$5001,">="& "31/12/2018")
 
Hi ,

What this will do is it will check for all cells in the range I6:I5001 which meet the following criteria :

The cell value should be <= 31-01-2014 AND >= 31-12-2018.

No cell value can meet this criteria. It is impossible.

Explain what you want to do , and the correct formula can be suggested.

Narayan
 
Am sorry,

Right formula is:



=COUNTIFS('NEW REGISTER'!$I$6:$I$5001,"<=12/31/2014",'NEW REGISTER'!$I$6:$I$5001,">=1/1/2014")
 
Upload sample workbook. When working with dates, how the data is stored in cells matters quite a bit. Without looking at what actual values are contained within the range, it's hard to give you help.
 
upload_2018-5-7_10-4-35.png

What is your system date set to? If it's set to dd/mm/yyyy, that could cause issues.

In that case, replace "<=12/31/2014" etc with "<=31/12/2014" etc.

However you can make formula more dynamic, by using following.
=COUNTIFS('NEW REGISTER'!$I$6:$I$5001,"<="&DATE(E4,12,31),'NEW REGISTER'!$I$6:$I$5001,">="&DATE(E4,1,1))

By using Date formula, it will accommodate for any other date format/region setting used (it won't work with text dates however).
 
You must log in or register to reply here.
Back
Top