Excel Excel - Countif Not Working! It misses some cells.

[Kiwi Berg]

Hello all,

I am having a maddening problem with excel's Countif function. It will count certain rows of data, but not others.

In example below, the "count" column has formulas where I drag them down so

C2 =COUNTIF($A$1:$A$2000,B2)
C3 =COUNTIF($A$1:$A$2000,B3)
C4 =COUNTIF($A$1:$A$2000,B4)

etc.

In column "B" I manually typed "8:00", "8:01" and "8:02" and used auto-fill for the rest, as it goes on and on up to 16:00.

A B C
1 data cond count
2 8:00 8:00 1
3 8:02 8:01 0
4 8:03 8:02 1
5 8:04 8:03 0
6 8:07 8:04 1

Notice in cell C5, that nothing shows up! Even though there is a value in column A of "8:03". 8:02 shows up, and so does 8:04. So why not 8:03?

Here's the kicker - if I go into cell B5 and MANUALLY TYPE IN "8:03", then it gets counted in C5!!!

Problem is, the data column A is 1100 cells long and column B is 720 columns. I can't manually type in each time condition 720 times in column B.

I have checked the format of all cells, and they are all set to the same format of Time (13:30).

What am I missing?!? This is such a powerful function, but why is it not working for me? Any help or suggestions are severely appreciated!


Cheers,
Kiwi Berg

 

[Kiwi Berg]

Solved it. Another forum helped me find the answer.

Excel treats times as numbers, with 24 hours being "1.0" and 12:00 being ".50".
Since 1:45 is really seen as 0.045138888888 (repeating) I needed to round all the times after I added and subtracted them.

So, I rounded all time values involved to 5 decimal places using a formula like
ROUND(A3, 5)

With all the times rounded, now they match and COUNTIF can detect them, just like it's sposed to!

Hope this helps somebody else out there...
Happy Holidays,
Kiwi Berg