Count negative cells ,count positive cells

Chowder13037

New Member
Joined
Apr 9, 2002
Messages
21
I have a column of cell with both negative numbers and positive numbers in the column. At the buttom I want to creat a formula that will count how many cells in the column have negative numbers and another formula that will count how many cells have positive numbers. The formula for the negative numbers will be in one cell and the positive in another cell .
Can someone help me with this .
Thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Here's the sloppy answer.

In a second column, for each cell to count
=if(A1<0,1,2)

Assuming the the above formula is Column B
=Countif(B:B,1) 'the negative values
=Countif(B:B,2) 'the positive values
This message was edited by ArthurReyes on 2002-12-10 14:32
 
Upvote 0
For the negative nos, you could use for example: -

=COUNTIF($A$1:$A$8,"<0") where A1:A8 houses your numbers. Use ">=0" as your criteria for positive numbers (or just ">0" if you want to exclude zeros.
 
Upvote 0
=COUNTIF($A$1:$A$8,"<0") where A1:A8 houses your numbers. Use ">=0" as your criteria for positive numbers (or just ">0" if you want to exclude zeros.

You know, I've always wondered if there was an easier way to do that! Awesome!
 
Upvote 0
For the negative nos, you could use for example: -

=COUNTIF($A$1:$A$8,"<0") where A1:A8 houses your numbers. Use ">=0" as your criteria for positive numbers (or just ">0" if you want to exclude zeros.
I registered here just to applaud this answer. So.. top stuff! Saved me a lot of time!
 
Upvote 0
Had a question regarding this, yes it helps to count the cell #'s that are positive and negative. But is there a way to be able to add the total of the amount for the negative and positive qty's. I am trying to compare pricing from one month to another month. To what is going in and what is coming in money wise.
 
Upvote 0
Try SUMIF instead of COUNTIF

=SUMIF($A$1:$A$8,"<0")
=SUMIF($A$1:$A$8,">0")
 
Upvote 0
Many thanks this helped out a lot much easier to get the negative amount and the positive differences
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top