COUNTIF between dates

[guy13]

Hi,

I am trying to do a countif between two dates using =COUNTIFS(A:A,">="&D9,A:A,"<="&E9) the trouble is what I am counting is in the format "27/07/2013 02:52:09"

See the attached $$, hope you can help? I'm sure there's a simple fix...

countif between two dates.xlsx

[DBY]

Hello
You can't apply the criteria >= <= to text, as there is no value to work with. Your column of dates and times are text values. You will have to convert these to numerical values. Are you able to do that with your actual data?

[TMS]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

using the Dynamic Named Range:

Please Login or Register  to view this content.

Regards, TMS

[guy13]

Hello
You can't apply the criteria >= <= to text, as there is no value to work with. Your column of dates and times are text values. You will have to convert these to numerical values. Are you able to do that with your actual data?

Unfortunately the data comes as is. I don't know if you can convert it?

[DBY]

As an alternative, you could use:

Please Login or Register  to view this content.

Here you would not need to convert the dates in the column, as SUMPRODUCT & VALUE do it for you.

[guy13]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

using the Dynamic Named Range:

Please Login or Register  to view this content.

Regards, TMS

This is great, but really I want to do this to the whole of A:A. After that works I want to add COUNTIF's statements like H:H,"Y". Not sure this can be done with the above formulas?

[guy13]

As an alternative, you could use:

Please Login or Register  to view this content.

Here you would not need to convert the dates in the column, as SUMPRODUCT & VALUE do it for you.

Again, I need to so this with A:A and eventually I want to add more countif statements like H:H,"Y". This is a tricky one...

[TMS]

You can't use SUMPRODUCT with entire columns ... well, strictly, you can if you have 2007 or above, but it is not recommended.

However, the second option, using the Dynamic Named Range, will cater for all the data in column A without having to cope with thousands of blank rows.

Have a closer look at the second formula and how the Named Range is defined, and then look at the updated example.

Regards, TMS

[DBY]

Trevor has given you an example of a dynamic range which grows as you add data. There is no need to reference the whole column, it's not very efficient and I believe SUMPRODUCT fails if you do that.

*Sorry Trevor didn't see your reply.

[TMS]

Please don't repeat whole posts; it just adds clutter. Refer to specific contributors and or relevant parts of the post.

Please Login or Register  to view this content.

nrColH:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

@DBY: no problem

[guy13]

Please don't repeat whole posts; it just adds clutter. Refer to specific contributors and or relevant parts of the post.

Please Login or Register  to view this content.

nrColH:

Please Login or Register  to view this content.

Regards, TMS

Could you send me these in the $$ please?

[TMS]

Updated example:

[guy13]

Updated example:

lovely thanks!

[TMS]

You're welcome.

[TMS]

Thanks for the rep.