View original (French)

Excel/open office - sum of numbers>0 and <

Solved
catcat -  
 Acim -
Hello everyone,
This is my first time posting here, and I feel particularly clueless, even after reading the various posts about conditional sums, etc...
Here's the little issue that's bothering me, but it bothers me because it shouldn't be that hard, yet I'm stuck...
I have in the same column some numbers >0 and others <0, and I would like to get (at the end of this column) the sum of the positive and negative numbers separately.
I've tried SUMIF but it seems to be stuck....
I'm using Open Office, but I think the formulas should be the same as in Excel.... it's just in my head where the logic isn't clicking!
If anyone could spare a bit of time to advise me, I would really appreciate it.
Have a great evening!
Configuration: Windows XP Opera 9.26

12 answers

  1. booddha
     
    A3 = 25
    A4 = -12
    A5 = 63
    A6 = -42

    A8 =SUMIF(A3:A6,"<0")
    A9 =SUMIF(A3:A6,">0")
    --
    If there is no solution, then there is no problem.
    22
    1. Abdellah BEN MOULA
       
      Thank you
      0
    2. Acim
       
      Thank you for the response!! It works great!
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Good evening,

    here is the syntax in Excel:
    =SUMIF($C$1:$C$12, ">0")
    and
    =SUMIF($C$1:$C$12, "<0")

    don't forget the " " around the test
    eric
    1
  3. booddha
     
    If I understood correctly (but I'm not sure)

    =IF(A1<b1 />
    Check the calculations by hand.
    --
    If there is no solution, it means there is no problem.
    1
  4. booddha
     
    Yeah, well, I won, by 5 seconds.

    Good luck to you and to Eriic as well

    ;-)
    --
    If there is no solution, then there is no problem.
    0
  6. catcat
     
    Uh, I have another one....

    A1=30
    B1=6
    C1=4

    Based on that, I would like the sum of row 1 taking into account the number in B1 if B1>C1 ...
    Attention, ready, set, go ...
    ... and thank you a thousand in advance!!!
    0
  7. booddha
     
    =IF(B1>C1,SUM(A1:C1),0)
    --
    If there is no solution, it means there is no problem.
    0
    1. catcat
       
      Thank you very much, booddha, but I think I will need to call on your expertise once again because it doesn't completely work in my formula line.
      I must say that I was poorly expressed (I think I oversimplified) in the previous request.
      In the meantime, have a great day!
      0
  8. catcat
     
    Well, here I am again with my faulty logic...

    I have 6 columns:

    Hours worked: 100
    Contract hours: 95
    Authorized absences: 9
    Sick leave: 3
    Training: 1
    Paid leave: 2

    I want to know the extra hours worked (or not, so in the result cell I want to obtain a negative or positive number), and in the particular calculation logic of the association I should do: 100 - (95 - 9 - 3 - 1 - 2) = 20 extra hours
    EXCEPT that I should only account for the reduction of authorized absences on contract hours WHEN hours worked < contract hours
    And I would like to have a formula that summarizes all this...
    And I've been pulling my hair over it for quite some time now... If anyone could prevent me from going bald or throwing my PC out the window, I would be really, really happy... and above all I could move forward in my job !!!!!
    Thanks for all the help you can provide
    Best regards
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      Just in case, since your subject is not very easy to understand, I suggest:

      =IF(A1<B1;A1-(B1-C1-D1-E1-F1);A1-(C1-D1-E1-F1)) Here, you only subtract the value of B1 when A1 is less than B1. Is that what you're looking for?

      BCRDLMNT
      --
      The truth is not possessed, it is only sought.
      0
      1. catcat > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         
        Yessss that's it!!! ... thank you very much and have a great rest of the day!
        0
      2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453 > catcat
         
        I’m glad for your computer and especially for your hair. I think it would have been a waste in both cases.
        Best wishes, feel free to come back and see us.
        CRDLMNT
        --

        The truth is not possessed; it is only sought.
        0
      3. catcat > Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention  
         
        Roooh what a charmer....
        Thank you again and see you soon!

        ... actually, when you have the solution, it's really very simple!!!
        0
  9. booddha
     
    to do: 100 - (95 - 9 - 3 - 1 - 2) = 20 overtime hours

    Well 100 - (95 - 9 - 3 - 1 - 2) = -10 overtime hours
    --
    If there is no solution, then there is no problem.
    0
    1. catcat
       
      It's part of my hair-pulling... (I'm almost at the point of slapping myself too)
      95-9-3-1-2 = 80 hours of contracts
      100 hours completed
      => 20 overtime hours... right?!?
      0
  10. Loladenice
     
    Hello,

    I have a problem with the SUMIF function. Here’s what I wrote:

    =SUMIF((G8:G17)-(E8:E17), "<=0", G8:G17) What I want is for the numbers in column G to be included in the sum of that column only if Gn-En<=0. I really don’t know how to do it; I think I’m not far off with this formula. Thank you. Lola

    Column E Column G
    50 670 129 924
    886 080 1 136 000
    28 381 72 772
    55 285 141 756
    115 672 296 595
    299 146 383 520
    378 000 630 000
    423 150 465 000
    65 910 169 000
    27 300 70 000
    Sum
    0
    1. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      Not far... but it still needs to be understood by SUMIF which line of G you want to subtract from a line of E?!
      To simply solve your problem, if you can use a return column out of the main field that you can hide at the end of the table, let's say for example column X:
      <bold>In X8=IF(G8>E8,G8,0)</bold>
      drag this formula down through the field
      Wherever you want your result, just sum up the X

      CRDLMNT

      --
      The truth is not owned, it is only sought.
      0
  11. loladenice
     
    Hello,
    Thank you for your response. I'm sorry, but I didn't quite understand the method you are recommending. I vaguely understood that I should add a column at the end of the table, but I was actually hoping to avoid that...
    0
  12. loladenice
     
    Sorry, upon rereading your message, I have understood your process which I had already considered. However, I wanted to do it in a single formula as I have several columns to analyze. This method unfortunately requires me to create another table next to my original table. Thank you.
    0
  13. catcat
     
    Well done!!! ... are you having a contest to see who can be the fastest or what???
    Anyway, thank you very much for your answers, even though I really feel foolish for not having figured it out myself...
    Thank you, thank you, thank you
    -1