View original (French)

Condition sum cell "rest" equals 0:00h

Solved
noupy64 -  
Ange420 Posted messages 437 Status Member -
Hello everyone,

I'm trying to create a monthly schedule with Excel and I'm stuck on the cells labeled REST. Is it possible to formulate a calculation knowing that if, for example, C6 and D6 are labeled REST, then the sum of the two should equal 0:00 hours?

Best regards

3 answers

  1.
    Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    I don't quite understand what you want to do. For example, if you want to add hours from cells whose values are "Rest", you just need to use the SUM syntax. For instance, to sum the cells from A1 to A20 containing hour values like 1:00 with "Rest" values, you would use =SUM(A1:A20)

    If the cells are not contiguous, for example A1, A5, A10, etc.,
    the syntax would be =SUM(A1;A5;A10)

    And if there are contiguous cells, for example from A1 to A5, then A10, A15, and from A20 to A25

    =SUM(A1:A5;A10;A15;A20:A25)

    However, if the total hours exceed 20:00, you will need to format the cell in the [hh]:mm format
    A+
    Mike-31

    A failure period is a perfect time to sow the seeds of knowledge.
    1
  2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Good evening

    and what if we tried:
    =IF(AND(C6="REST",D6="REST"),0,IF(C6="REST",D6,IF(D6="REST",C6,D6+C6)))

    Best regards
    --
    Let's ask ourselves if we are not alone in understanding what we are explaining?
    1
  3. Ange420 Posted messages 437 Status Member 236
     
    This works very well, look at the example
    https://www.cjoint.com/?3bisAbeJLvC

    --
    What is learned with effort is not easily forgotten; What is learned and acquired easily is quickly forgotten and dissipated!
    0
    1. Ange420 Posted messages 437 Status Member 236
       
      =somme(A1+B1) in my example
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Excellent Angel, at least this proposal allows for the identification of a new Excel mystery and for my part, I thank you.

      Your solution doesn't work as you wrote it in your message, namely =SUM(A1+B1)
      nor does A1+B1
      when there is text in one of the summed cells.

      However, it works very well if you write it as in the model you propose, that is:
      =SUM(A1:B1)
      with the colon instead of the +

      Thank you for making me (us?) discover it!

      Have a great Sunday.
      0
    3. Ange420 Posted messages 437 Status Member 236
       
      Indeed Vaucluse, in my haste I put a ""+"" in my comment instead of ":"
      And since the file will disappear over time in column C, the formula is indeed =sum(A1:B1). As for =A1+B1 it gives a #value if it encounters text
      A ******* B ****** C
      02:00 04:00 06:00
      04:00 REST 04:00
      06:00 04:00 10:00
      08:00 REST 08:00
      Thank you for correcting the error in my comment for those who will follow ;-)
      It's important because the sum function adds the numbers contained in a range, so it ignores cells that do not contain numbers like here with text.
      Happy New Year Vaucluse, I haven't wished you yet.
      0