Count negative cells ,count positive cells

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Count negative cells ,count positive cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    43,753
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    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")
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    43,753
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count negative cells ,count positive cells

      
    Glad to help, thanks for the feedback.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    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

User Tag List

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