View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default How do I sum increments of minutes and seconds

Sandy,
try this:

=SUM(TIMEVALUE("0:"&LEFT(A1:A3,FIND(".",A1:A3)-1)&":"&MID(A1:A3,FIND(".",A1:A3)+1,LEN(A1:A3))))

This is an array formula, hence it should be committed with
Shift+Ctrl+Enter.

The problem with this formula is that you have to specify the range
A1:A3 exactly in several parts, and it will give you a #VALUE! error if
applied to a bigger range than you have data, b/c of the blank cells. A
sightly more complext alternative,

In a separate cell (say C3) enter the range that you want to sum. E.g.
C3 contains text A1:A3

Then you can use the following (again array) formula

=SUM(TIMEVALUE("0:"&LEFT(INDIRECT(C3),FIND(".",IND IRECT(C3))-1)&":"&MID(INDIRECT(C3),FIND(".",INDIRECT(C3))+1,L EN(INDIRECT(C3)))))

HTH
Kostis Vezerides