- You are here
- Microsoft Newsgroups Archived.At
- microsoft.public.excel.misc
- 2006 May
- COUNTIF with non-zero format?

Subject: | COUNTIF with non-zero format? |

Posted by: | LTUser54 (LTUser54.28bv1m_1148500500.975@excelforum-nospam.com) |

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.

Mark

Boston

--

LTUser54

------------------------------------------------------------------------

LTUser54's Profile:http://www.excelforum.com/member.php?action=getinfo&userid=33459

View this thread:http://www.excelforum.com/showthread.php?threadid=545250

- RE: COUNTIF with non-zero format? posted by Peo Sjoblom on Wed, 24 May 2006