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
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_a2.xlsx32 KB

NikolinoDE

Platinum Contributor

Oct 11, 2020

habib_a

With the permission of all involved, if you allow me to add this additional information to the very good explanation from Mr. @Riny_van_Eekelen , where all the different possibilities of time counting are explained in detail by Microsoft.