Forum Discussion

rangelsammon's avatar
rangelsammon
Brass Contributor
Mar 17, 2023
Solved

sum, sumif, and sumifs h:mm.ss

Running into an issue and cant understand whats going on   trying to sum, sumif, sumifs times https://1drv.ms/x/s!AnFi6uGE1rekjwnBbupqs1CUxXHg?e=zCUDhF
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Mar 17, 2023

    rangelsammon 

    All 'time values' in C are texts. Convert them to real time values first. Select column C. Then on the Data ribbon select Text-to-columns and press Finish without changing anything.

     

    Now the formulas in D will work, though I wonder why you want to present the outcome within a TEXT formula. Much easier to just use the SUM, SUMIF and SUMIFS and specify a custom format as [h]:mm:ss

     

    The square brackets surrounding the h will allow you to keep summing in case the sum would ever exceed 24 hours.

    See attached file.

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Mar 17, 2023

rangelsammon 

All 'time values' in C are texts. Convert them to real time values first. Select column C. Then on the Data ribbon select Text-to-columns and press Finish without changing anything.

 

Now the formulas in D will work, though I wonder why you want to present the outcome within a TEXT formula. Much easier to just use the SUM, SUMIF and SUMIFS and specify a custom format as [h]:mm:ss

 

The square brackets surrounding the h will allow you to keep summing in case the sum would ever exceed 24 hours.

See attached file.

 

Resources