Count or Negative and Positive #'s

[LVMan]

What would the formula to count (not the sum) negative numbers within a column and then postive numbers in the same column?

Thank you!!

IE:
-1
2
2
-5
-6

3 negative
2 positive

 

[Mark W.]

=COUNTIF(A1:A5,"<0")
=COUNTIF(A1:A5,">0")

 

[LVMan]

Thank you Mark.. I really appreciate your help.

Do you know what the formual is for converting Caps to proper name lower case?

JOHN H SMITH to John H Smith

And is it possible to eliminate . in initials as I never use periods......

Thank you!!!

 

[bhmcpfs]

PROPER worksheet function should do the trick for the capitolization.

 

[bhmcpfs]

As for the ".", highlight data cells to manipulate, and use Edit, Replace (Ctrl+H). In 'Find What' field, put the "." (no quotes), then click Replace All.

 

[LVMan]

Thank you!!

I guess I just had a brain fart for a second and could not think..

 

[LVMan]

Can you help again? I am trying to to a countif formula subtracting the > form the < in the same columns

=COUNTIF(I2:J78,">0")

MINUS

=COUNTIF(I2:J78,"<0")

 

[anno]

=COUNTIF(I2:J78,">0")-COUNTIF(I2:J78,"<0")
or just use the cell refs these formulae are in eg. if they are in A1 and B1, use =A1-B1
This message was edited by anno on 2002-04-08 23:36

 

[Peo08]

On 2002-04-08 23:22, LVMan wrote:
Can you help again? I am trying to to a countif formula subtracting the > form the < in the same columns

=COUNTIF(I2:J78,">0")

MINUS

=COUNTIF(I2:J78,"<0")

Try

=SUMPRODUCT((I2:J78>0)-(I2:J78<0))

Regards,

Peo Sjoblom