Wrong Sum-up values from a range with xlookup and index+match formula

Hi everyone!

Good day!

I have an issue having the correct summation for the ranges with xlookup or index+match formulas. the below screenshot , if you can see the total for the those values from 1-31, the correct answer when summed up is 47:26:59, it gives me an incorrect answer. Do you have any idea why?

this is what my formula from 1 to 31, referencing the column heading.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

thanks in advance, i have attached an excel file for fast reference

The current formula using the SUMIF() function =SUMIF(E5:AJ5,0,E5:AJ5) is summing all of the entries where the value is 0. Of course, when you sum a bunch of 0 values, you get 0.

I edited the function to sum when the value is greater than 0 =SUMIF(E5:AJ5,">0",E5:AJ5) and that seemed to fix the summation issue. I noted that your "time of day" format h:mm:ss displayed 23:26:59, so I formatted as elapsed hours [h]:mm:ss to get the display to show 47:26:59.

I would also note that 0 does not affect a sum, so I also tried copying the SUM() function in the row below, and that also worked =SUM(E5:AJ5) (again, formatted as elapsed hours).

Is that what you are looking for?

HI MrShorty,

Good evening to you! The suggestion works perfect! tried this already =SUMIF(E5:AJ5,">0",E5:AJ5) , however [h]:mm:ss this fixed the issue! Thank you so much!