Count or Negative and Positive #'s

LVMan · Apr 3rd, 2002, 01:29 PM

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. · Apr 3rd, 2002, 01:35 PM

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

LVMan · Apr 3rd, 2002, 02:18 PM

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 · Apr 3rd, 2002, 02:29 PM

PROPER worksheet function should do the trick for the capitolization.

bhmcpfs · Apr 3rd, 2002, 02:32 PM

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 · Apr 3rd, 2002, 02:36 PM

Thank you!!

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

LVMan · Apr 9th, 2002, 12:22 AM

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 · Apr 9th, 2002, 12:34 AM

=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 · Apr 9th, 2002, 12:54 AM

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 3rd, 2002, 01:29 PM	#1
LVMan New Member Join Date: Apr 2002 Posts: 49	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

Apr 3rd, 2002, 01:35 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=COUNTIF(A1:A5,"<0") =COUNTIF(A1:A5,">0")

Apr 3rd, 2002, 02:18 PM	#3
LVMan New Member Join Date: Apr 2002 Posts: 49	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!!!

Apr 3rd, 2002, 02:29 PM	#4
bhmcpfs Board Regular Join Date: Apr 2002 Posts: 88	PROPER worksheet function should do the trick for the capitolization.

Apr 3rd, 2002, 02:32 PM	#5
bhmcpfs Board Regular Join Date: Apr 2002 Posts: 88	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.

Apr 3rd, 2002, 02:36 PM	#6
LVMan New Member Join Date: Apr 2002 Posts: 49	Thank you!! I guess I just had a brain fart for a second and could not think..

Apr 9th, 2002, 12:22 AM	#7
LVMan New Member Join Date: Apr 2002 Posts: 49	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")

Apr 9th, 2002, 12:34 AM	#8
anno Board Regular Join Date: Feb 2002 Posts: 202	=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 ]