Help with COUNTIFS formula when counting dates that include a time

[UpToTheGills]

Morning all,

I am attempting to use COUNTIFS to total the number of trades for a particular month on a different worksheet but the formula I have used just returns a zero. I wondering if that is because I need to take account of the dates to count also include the time?

=COUNTIFS('IC Live 4004xxxx'!$B$3:$B$353,">="&C5,'IC Live 4004xxxx'!$B$3:$B$353,"<="&EOMONTH(C5,0))

I can't figure out what is incorrect about the above formula…

The dates that I want to count appear formatted as below:

Thank you for any assistance.

 

[UpToTheGills]

If you change the format for the whole of col D to General do you see numbers or dates?

Numbers.

Let me just try "<"&Eomonth(C5,0)+1 and I will revert…

 

[UpToTheGills]

=COUNTIFS('IC Live 4004xxxx'!$D$3:$D$353,">="&C5,'IC Live 4004xxxx'!$D$3:$D$353,"<"&EOMONTH(C5,0)+1)

Just tried the above but still shows as zero.
Strange, looks fine to me, must be something real simple that I am overlooking.

 

[Fluff]

From the mini-sheet in post#9 the problem is that C5 is empty, the date is in B5 which is a merged cell (yet another reason why should never use them).

 

[UpToTheGills]

Thanks both. Yes - was referencing the incorrect cell. Formula now works.

I merged the cell so that the data would display where I wanted it for purposes of maximum legibility but it doesn't need to be merged so I will un-merge them all.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[UpToTheGills]

Glad we could help & thanks for the feedback.

If it is possible to mark more than one post as the solution, I will but I don't believe it is.

Strange, looks fine to me, must be something real simple that I am overlooking.

Never a truer word spoken.

 

[Fluff]

No it's not possible, only one post can be marked as the solution.

 

[etaf]

glad you have it all sorted now
Interesting about the EOMONTH() , i never tested that , but I can see how that would be an issue with time , noted for future - thanks Fluff

 

[UpToTheGills]

Much appreciated, especially the ease with which it was possible to upload a sample of the spreadsheet using XL2BB and the speed with which replies came and a solution eventually found.

Plus all the more better that the solution was the result of a simple mistake arising out of prioritising display/formatting over the actual data but it's good to be reminded of best practice to avoid them in the first place.