I have a COUNTIFS formula that is counting the number of records where the created date is <= a date selected by the user: =COUNTIFS(Data!$K:$K,$B86,Data!$AB:$AB,"",Data!$P:$P,"<="&EOMONTH(National!$W$1,0))This part of the formula isn't counting one of my records:

,"<="&EOMONTH(National!$W$1,0))The date contained within

National!$W$1is 31/07/2018 00:00The record that

isn'tbeing counted has the created date 31/07/2018 22:52With the formula's construction, shouldn't it count everything less than, or equal to 31/07/2018 23:59:59?

Without knowing what is in column K and what is in cell B6 can't really tell you what's going on. Just remember that

all three criteria must be matchedbefore it counts.Assuming that column AB is completely blank, no data,

If there is only One match in column K,

but multiple matches in column P

you only get a count of 1and conversely

if you have multiple matches in column K

buy only only one match in column P

you still only get a count of 1MIKE

Another thought,

With the formula's construction, shouldn't it count everything less than, or equal to 31/07/2018 23:59:59?If the date is really a Date and NOT Text that LOOKS like a Date.

Simplest way to check would be to select the entire P column

and then format as Number, if any remain in a Date Format that is where the error is.

Also, if any of the data in column K has a trailing space or special character

that could also result in a non match, even tho they look the same.MIKE

Hi Mike Thanks for the responses. I've checked and the data in the column is all dates. I've also checked the other criteria in the COUNTIFS (actually did this before posting but did it again to be sure) and there are no rogue spaces anywhere. As soon as I change the entry from

31/07/2018 22:52to31/07/2018 00:00the count changes from 1 to 2.I double checked by opening a new sheet and manually typing 31/07/2018 22:52 into cell A1, then typing 31/07/2018 00:00 into B1, then converting both to numbers. You can see that 31/07/2018 22:52 is actually a larger number than 31/07/2018 00:00 which makes sense.

What surprises me though is that when I typed

=EOMONTH(A1,0)into cell C1, it returns 31/07/2018 00:00. When I played around with dates/times and converted them to numbers it looks like the decimal increases with each 15 minute increase, i.e. to get 43312.98 you have to enter 31/07/2018 23:30 and to get 43312.99 you have to enter 31/07/2018 23:45.I'll therefore adjust my original COUNTIFS formula as follows to ensure I capture everything from the same date:

=COUNTIFS(Data!$K:$K,$B86,Data!$AB:$AB,"",Data!$P:$P,"<="&EOMONTH(National!$W$1,0)+0.99)Basically adding the 0.99 at the end.

message edited by ScottV

Scott, "Excel stores dates and times as a number representing the number of days since 1900-Jan-0,

plus a fractional portion of a 24 hour day."For a complete explanation of how Excel stores Dates & Times see here:

http://www.cpearson.com/excel/datet...

Scroll all the way to the top.

In my test sheets, I was not entering any times with the dates, so, I unfortunately, missed it.

MIKE

Ask Your Question

Weekly Poll

Do you think cloud computing will replacing gaming consoles?

Discuss in The Lounge

Poll History