Countif Problems

[RachL2711]

Hi, I’m having trouble with a countif formula. I’m wanting to count numbers in column B if column A has 23:59:00. The info in column A has the date and the time, so 21-Dec-2017 23:59:00, I’ve tried the below but it’s not working, it either counts all of column B or just column A.

=COUNTIFS(A9:A200,”*23:59:00*”,B9:B200,”<>”)

 

[RoryA]

Welcome to the forum.

You can't use wildcards with numbers (Dates and times are stored as numbers in Excel). You could use SUMPRODUCT like this:

=SUMPRODUCT((hour(A9:A200)=23)*(minute(A9:A200)=59)*(B9:B200<>""))

 

[RachL2711]

That would make sense why it isn’t working! I’ve tried the formula above though and it’s still just returning 0.