Nth Row and Sub total

**teylyn** · 01-11-2011, 09:52 PM

Ah, now I see. These formulas are summing rows, not columns.

Create the first row with the sum formula, copy the whole row (by selecting the row header), then use Insert Copied Cells to paste into their new locations (a new row will be inserted)

**DonkeyOte** · 01-12-2011, 04:30 AM

Perhaps simplest to just use Excel's inbuilt Subtotal functionality:

Highlight A:H and apply Data -> Subtotal as follows:

At Each Change In: Half Hour

Use Function: Sum

Add Subtotals To: Calls, Abandoned Calls, Avg Speed of Answer, Avg Abandoned Time

The other alternative would be to use a Pivot Table to analyse the Data

**blarp1** · 01-12-2011, 01:43 PM

that works great...Looks like I need to do some formatting in the cells that have hours, minutes, and seconds. This spreadsheet is a transfer from a business objects query and just changing the format does not allow the cells to be summed. Need to retype the number and then the summation works... I have changed the cell format from a general number to a custom h:mm:ss and the cell show that but still does not allow summation.

**DonkeyOte** · 01-12-2011, 02:12 PM

Prior to adding the Subtotals...

Type 0 into a blank cell - copy it - highlight your "Time" cells and run Paste Special -> Operation: Add

If you have just one column for the above you can also try highlight the column and run Data -> Text to Columns -> click Finish.
(TtC is limited to one column at a time so for multiple the Paste Special route can be preferable)

You might want to format as [h]:mm if any cell (incl. sub totals) can exceed 24 hours.