Is there any easy way to tally up the minutes and seconds?

[Burrgogi]

I've been watching a TV series and recorded the runtime of each episode. In column A, I have something like:

52:32
53:26
52:44
etc.

I'd like to subtract 1 min and 38 sec from each of these numbers and get a grand total at the end.

I could easily calculate it by hand but I have nearly ~70 of these and that would take quite some time. Is there any way to do this using a formula?

 

[AhoyNC]

Try:

Book1
	A	B	C
1	Time		Subtract from each time
2	52:32:00		0:01:38
3	53:26:00		Grand Total
4	52:44:00		158:37:06
Sheet2

Cell Formulas
Range	Formula
C4	C4	=SUM((A2:A4-C2))

 

[Ezguy4u]

Because you can never have too many answers. This subtracts 1 minute and 38 seconds from each of the numbers. I am going to say you should custom format your cells with [h]:mm.

20-09-15 time.xlsx
	A	B	C	D
1	Time		Subtract from each time
2	52:32		1:38	50:54
3	53:26			51:48
4	52:44			51:06
5			Grand Total	153:48
Time

Cell Formulas
Range	Formula
D2:D4	D2	=A2-$C$2
D5	D5	=SUM(D2:D4)

 

[Burrgogi]

@AhoyNC

Shouldn't your formula contain an absolute reference like so?

=SUM((A2:A4-$C$2))

Because you can never have too many answers. This subtracts 1 minute and 38 seconds from each of the numbers. I am going to say you should custom format your cells with [h]:mm.

Oh wow, I can't believe that I almost forgot about formatting the cells in the proper time format. Thanks for the reminder.

 

[AhoyNC]

Burrgogi - Since my formula is not copied to any other cells there is no need for an absolute reference. This part of my formula "(A2:A4-C2)" returns the same array of values as your helper column without the need for the extra column. Also your value of 1:38 in cell C2 is actual subtracting 1 hour and 38 mins. from the times and not 1 minute 38 seconds.

 

[Fluff]

Judging by the title & the fact that the times are per episode, maybe this would be correct.
Depends on exactly how the times have been entered

+Fluff New.xlsm
	D	E
6		00:01:38
7	52:32:00
8	53:26:00
9	52:44:00
10
11		2:33:48
Master

Cell Formulas
Range	Formula
E11	E11	=SUM((D7:D9/60-E6))

 

[mikerickson]

And a non-array formula for the sum might be like

=SUM(A1:A10)-B1*COUNT(A1:A10)

 

[Burrgogi]

Judging by the title & the fact that the times are per episode, maybe this would be correct.
Depends on exactly how the times have been entered

=SUM((D7:D9/60-E6))

I was interested in trying out your formula but I'm getting a value error. The cells have been formatted as [h]:mm as suggested by Ezguy4u.

 

[Fluff]

What exactly is in the cell & is 52:32 meant to be 52 hours 32 mins or 52 mins 32 secs

 

[Burrgogi]

And a non-array formula for the sum might be like

=SUM(A1:A10)-B1*COUNT(A1:A10)

Nailed it right on the head. Exactly the total I was hoping to get. Thanks for that one.