Why is my COUNTIF formula returning “0”?

You have a lot going on with your tables. Often it helps to get them working first and then add formatting, avoiding merged cells where possible.

Quite likely part of your problem here relates to the fact that Numbers deals in Date & Time. Even when you display just the time, the date is still there in the cell. It's just hidden from view.

So when you use =TIME(15,20,0) it seems that your result should be just 3:20 PM and you should be able to compare to the 3:20 PM in your Employee Count table.

The problem is that that each of the cells has an explicit or assumed date and there will be no match if those dates differ.

The date that Numbers supplies when you use =TIME(15,20,0) may be a little older than you expected!

And the date in each cell in column A of your 'Employee Count' table, if you entered those "times" by hand rather than deriving them by formula, is the date on which you entered that time.

To avoid problems like this I usually format cells where I want "times" as Text. That forces Numbers to assume the current date. Where you are using the TIME() function you could have Numbers assume the same date by using =TODAY()+TIMEVALUE(TIME(15,20,0)). Then you should get your match as you expected.

Entering the "times" as Text in both tables (so Numbers assumed today's date in both) I get the expected results when I do something like this:

In B2, filled down:

=COUNTIFS(Schedule::$B,"<="&$A2,Schedule::$C,">"&$A2)

I used > instead of >= , which assumes the employee is no longer there at clock out time.

SG

SG

In your first screenshot are you sure the less than/greater than operations are correct?

Try changing them in the CountIfs formula so the first one is "greater then or equal to" and the last one is "less than or equal to". Basically just switching the first one with the last one.

SGIII wrote:

I used > instead of >= , which assumes the employee is no longer there at clock out time.

Good catch SG 😎

Ian.