Formula sum

%3CLINGO-SUB%20id%3D%22lingo-sub-1528226%22%20slang%3D%22en-US%22%3EFormula%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528226%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20change%20the%20way%20a%20row%20sums...%3F%3F%3F%20Example%3A%20the%20cells%20are%20in%2015%20min%20increments%20as%20.15.%20How%20do%20I%20get%20the%20sum%20to%20turn%20whole%20at%2060%20instead%20of%20100%3F%3F%20Does%20that%20make%20sense%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1528226%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528264%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528264%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20possibility.%20Change%20range%20reference%20as%20needed%3A%20%3DSUMPRODUCT(TIME(INT(A1%3AA10)%2C100*MOD(A1%3AA10%2C1)%2C0))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20traditional%20time%20format%20(h%3Amm)%20or%20use%20a%20custom%20number%20format%20h.mm%20if%20you%20want%20to%20continue%20using%20a%20decimal%20instead%20of%20colon%20to%20separate%20hours%20and%20minutes.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

How do I change the way a row sums...??? Example: the cells are in 15 min increments as .15. How do I get the sum to turn whole at 60 instead of 100?? Does that make sense??

1 Reply
Highlighted

One possibility. Change range reference as needed: =SUMPRODUCT(TIME(INT(A1:A10),100*MOD(A1:A10,1),0))

 

You can use the traditional time format (h:mm) or use a custom number format h.mm if you want to continue using a decimal instead of colon to separate hours and minutes.