Announcement

Collapse
No announcement yet.

EXCEL Question - Total negitive values ONLY

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • EXCEL Question - Total negitive values ONLY

    I have a sheet that has 7 cells I want to total. But only total them when they are negitive values. for example, If the 7 cells are "1,0,-1,2,-2,3,-1" I want it to come back and give a positive result of 4 (only totaling the -1,-2,-1). is this possible. (See the pic attached for the example)

    OR

    Have 1 field, when it is a negitive value, the field below it shows that in a value positive otherwise the lower field is 0



    thanks
    Tekjock
    Attached Files

  • #2
    Re: EXCEL Question - Total negitive values ONLY

    Look at the SUMIF function, which will add values meeting a specific criterion
    If your numbers are in A1:A7,
    =-SUMIF(A1:A7,"<0")

    Note the initial minus sign is to make the result positive
    the criterion "<0" must be in quotes

    Tom
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment


    • #3
      Re: EXCEL Question - Total negitive values ONLY

      Ok ... that is with a consecutive range. what if the cells are not next to each other (see SceenCap)

      Is there a way to do this. the way the formula is written it will not work. If I have to do the calculations in a hidden column and write it back I can, but is this possiable to do wit the cells not next to each other
      Attached Files

      Comment


      • #4
        Re: EXCEL Question - Total negitive values ONLY

        =-SUMIF(B2:J2,"<0")

        Try that.
        ** Remember to give credit where credit is due and leave reputation points where appropriate **

        Comment


        • #5
          Re: EXCEL Question - Total negitive values ONLY

          I did that, it is still taking the range between the 2 cells. and not each cell.

          Comment


          • #6
            Re: EXCEL Question - Total negitive values ONLY

            Whats in the missing cells?

            Could you explain what you are trying to do here -- it might help us to help you!
            Tom Jones
            MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
            PhD, MSc, FIAP, MIITT
            IT Trainer / Consultant
            Ossian Ltd
            Scotland

            ** Remember to give credit where credit is due and leave reputation points where appropriate **

            Comment


            • #7
              Re: EXCEL Question - Total negitive values ONLY

              Originally posted by tekjock View Post
              I did that, it is still taking the range between the 2 cells. and not each cell.
              it worked for me.
              ** Remember to give credit where credit is due and leave reputation points where appropriate **

              Comment

              Working...
              X