Forum Discussion

habib_a's avatar
habib_a
Copper Contributor
Oct 10, 2020
Solved

somme.si avec comme critere le temps

j'utilise somme.si pour additionner le temps et je prend comme critère le temps lui même (en minute) mais j'ai des résultat erroné et merci d'avance par exemple je veut avoir la somme des temps supér...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Oct 11, 2020

    habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Oct 10, 2020
habib_a Is it perhaps so the the sum of the times becomes greater than 23 hours and 59 minutes? If so, make sure to custom format the cell that contains the end result as [h]:mm or [mm] to display the duration as hours and minutes or as minutes alone.
 
For instance, if the total duration equals 2108 minutes, use custom format [mm] to display as 2108. Use [h]:mm to display 35:08
 
If the cell is formatted as Time (as in clock-time) or as hh:mm:ss, Excel will display, 11:08:00 being the number of "clock" hours after midnight.
 
See the attached workbook for an example.
habib_a's avatar
habib_a
Copper Contributor
Oct 11, 2020

Riny_van_Eekelen  

thank you for your support but the problem is not due to this tip here is a file extract to better understand the situation

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Oct 11, 2020

    habib_a You stumbled an a floating point error (Google for that if you want to know more about it). Excel has problems with very large and very small numbers. Enter 10 minutes by itself and display it as a number with more than 15 decimals. Then compare it with the 15 decimal number of the calculated value of 10 minutes (thus an end time minus a start time) and you will note that they are not the always the same. In your case, SUMIF missed quite a few rows with "calculated 10 minutes" were just a fraction smaller that the "clean 10 minutes". Solved that by rounding the calculated duration, as well as the criteria, to fewer decimals,. Now the SUMIF and the manual calculation give the same result. See attached.

    • habib_a's avatar
      habib_a
      Copper Contributor
      Oct 11, 2020

      thank you Mr Riny_van_Eekelen for your support it will be a great help to me and very useful.

      Knowledge is a treasure. thanks again