COUNTIF with non-zero format?

Giganews Newsgroups
Subject: COUNTIF with non-zero format?
Posted by:  LTUser54 (
Date: Wed, 24 May 2006

Hey brainiacs! I have this odd formatting/COUNTIF problem and I hope you
can help. I have 3 columns of numbers - all in plain US Dollars with
whole cents, i.e. $5327.42

Each column is about 400 items, one is formatted in negative numbers,
the other (to it's right) with positive numbers, and most (but not all)
of the numbers match. They all look like regular dollar amounts when
each cell is examined.

I wanted to find variances and did that with a simple sum of Col. 1 +
Col. 2 = $0.00  in Col. 3, with any non-zero number sticking out.

Here's the problem. I checked this Col 3 list and found 11 non-zero
items. When I use COUNTIF, i.e.:
=COUNTIF(O4:O413,">0")+COUNTIF(O4:O413,"<0") I get a count of 12!!!

I copied & pasted all numbers from Col 3 as Values and found one is
4.547 to the -13. but all numbers are formatted as currency with 2
decimal places, BTW that one cell reads $0.00 as a sum of 3 numbers.
Can ANYONE tell me why this is happening, and how I can avoid this?? I
feel like I'm in the Twilight Zone here....  Thanks.


LTUser54's Profile:
View this thread: