# Solved: Excel Problem: COUNTIF not working - counting some data but not all!



## Kiwi Berg (Dec 22, 2008)

Hello all,

There's a ghost in the machine! I'm running Excel '97 on Windows XP. In using the COUNTIF funcition, It will count certain rows of data, but not others. 

Check this out...

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!

Much respect,
Kiwi Berg


----------



## Zack Barresse (Jul 25, 2004)

Hi there, and welcome to the board!

Problem with numbers, specifically times, is that what you see may not actually be what the value is. One thing you MUST know is that a date/time is only a number. One whole day is one whole number. So a 24 hour period is equal to 1, while a half day is 0.5 value. So, if you're looking for the differences of a one minute value, is the difference of 0.00069444444 (rept'd). So it's a miniscule difference. You are obviously not showing any millisecond formats either. If you checked the numbers, would they all show the same? I.e. if you change all of their formats to numerics or general (select range, hit Ctrl + Shift + ~ , the ~ is directly left of the 1 key on most keyboards).

Also, I'm not sure what you mean. You don't want to manually input data down 720 rows? You don't have to. It's called Auto Fill. Not sure what you're filling, values or formulas. Values, if you're looking for a series, enter the first two in the series, select both cells, hover your cursor over the bottom right of the selection (your cursor will change to a bold *+* sign), double click it (it will copy them down as far as the data is in the left adjacent column). For a formula, you only need to enter it once and repeat the same process (double clicking lower right corner).

HTH


----------



## turbodante (Dec 19, 2008)

Kiwi Berg said:


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


Have you tried using a different format, perhaps 00:00:00?

Which seems to work okay for me (on Access 2007).


----------



## slurpee55 (Oct 20, 2004)

The problem is one of rounding - for instance, Excel converts both 0.339583333 and 0.339583334 into 8:09:00 AM, but, obviously they aren't a match.
In two other columns, enter =ROUNDUP(A2,10) and =ROUNDUP(B2,10) and fill down. Copy and Paste Special, Values.
Then enter your COUNTIF statement in the next column. based on these two columns. Everything should work.


----------



## Zack Barresse (Jul 25, 2004)

turbodante said:


> Which seems to work okay for me (on Access 2007).


1 application and 10 years difference here my friend.


----------



## turbodante (Dec 19, 2008)

Zack Barresse said:


> 1 application and 10 years difference here my friend.


 How embrassing. I meant Excel 2007.


----------



## Zack Barresse (Jul 25, 2004)

turbodante said:


> How embrassing. I meant Excel 2007.


Ah! Gotcha! Yeah, it works for me too. I think we need more information from the OP such as what the _actual_ values in the cells in question are. I think slurpee is hitting it on the head though..


----------



## slurpee55 (Oct 20, 2004)

I am using 2003, and was able to replicate the problem. So it was fairly easy to figure out a fix. Boils down to looking at the data as Excel does, rather than as formatted.
And I'm used to hitting heads - mostly my own....


----------



## Kiwi Berg (Dec 22, 2008)

Brilliant! You guys are brilliant.

Thanks for the warm welcome Zack, and for explaining how Excel actually sees the time values. And thanks Slurpee, for that rounding tip. Together they solved the problem!

One note: I used ROUND instead of ROUNDUP because when all the numbers were rounded up, some were still being missed by COUNTIF

Also, rounding to 5 decimal places seemed to work great. Only rounding to 4 places would change some of the time values.

So now that my data and my comparison data are all ROUND'ed, the COUNTIF function actually counts them!

 Thanks heaps! 
Kiwi Berg


----------



## slurpee55 (Oct 20, 2004)

Glad we could help! 
It is always good to remember that Excel doesn't look at data as humans do. For instance, today, 12/23/2008 is looked at by Excel as 39805. Tomorrow is 39806. Time is handled in a similar way.
If you ever need to know how Excel is "viewing" something, just format the cell as General.


----------



## Zack Barresse (Jul 25, 2004)

Ctrl + Shift + ~

I love my keyboard shortcuts for changing the format quickly...

Number: Ctrl + Shift + 1
Time: Ctrl + Shift + 2
Date: Ctrl + Shift + 3
Currency: Ctrl + Shift + 4
Percent: Ctrl + Shift + 5
Scientific: Ctrl + Shift + 6
BorderAround: Ctrl + Shift + 7


----------



## slurpee55 (Oct 20, 2004)

Can't teach an old dog new tricks, Zack...I barely Ctrl-C and Ctrl-V for cutting and pasting!


----------



## Zack Barresse (Jul 25, 2004)

Did I mention there was going to be a test for you Loche?


----------



## slurpee55 (Oct 20, 2004)

Oh goody, I love tests!


----------

