Count or Negative and Positive #'s
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Count or Negative and Positive #'s

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

    Default

     
    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Default

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

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PROPER worksheet function should do the trick for the capitolization.

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

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

    Default

    Thank you!!

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

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

    Default

    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")

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Default

      
    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

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