Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Count negative cells ,count positive cells

This is a discussion on Count negative cells ,count positive cells within the Excel Questions forums, part of the Question Forums category; I have a column of cell with both negative numbers and positive numbers in the column. At the buttom I ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    21

    Default

    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

  2. #2
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    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 ]

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Dec 2002
    Location
    Chicago, IL
    Posts
    77

    Default

    =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!

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    17

    Default Re: Count negative cells ,count positive cells

    Awesome! Was looking for this solution and found it here. Thanks!

  6. #6
    New Member
    Join Date
    Mar 2013
    Posts
    1

    Default Re: Count negative cells ,count positive cells

    Quote Originally Posted by Mudface View Post
    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!

  7. #7
    New Member
    Join Date
    May 2013
    Posts
    2

    Default Re: Count negative cells ,count positive cells

    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.

  8. #8
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default Re: Count negative cells ,count positive cells

    Try SUMIF instead of COUNTIF

    =SUMIF($A$1:$A$8,"<0")
    =SUMIF($A$1:$A$8,">0")
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  9. #9
    New Member
    Join Date
    May 2013
    Posts
    2

    Default Re: Count negative cells ,count positive cells

    Many thanks this helped out a lot much easier to get the negative amount and the positive differences

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    30,414

    Default Re: Count negative cells ,count positive cells

    Glad to help, thanks for the feedback.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com