software

Column A has dates eg: 2006/01/01, 2006/01/02 with many entries for each date.
Column B, C, D has data of hours used on these dates.
I am looking for a way to get the sum of each column specific to a date
(daily totals)

Why don't you just add a column to the right of your data with a sum function ?
quot;huffmjbquot; wrote:

gt; Column A has dates eg: 2006/01/01, 2006/01/02 with many entries for each date.
gt; Column B, C, D has data of hours used on these dates.
gt; I am looking for a way to get the sum of each column specific to a date
gt; (daily totals)

If you sort by column A, you can use Data|subtotals to add up those hours.

If the hours are really cells containing time (5:00 or 7:30), then format the
subtotals as [h]:mm.

It'll avoid a problem if the subtotals exceed 24 hours.

Or you may want to look into Data|pivottable.

huffmjb wrote:
gt;
gt; Column A has dates eg: 2006/01/01, 2006/01/02 with many entries for each date.
gt; Column B, C, D has data of hours used on these dates.
gt; I am looking for a way to get the sum of each column specific to a date
gt; (daily totals)

--

Dave Peterson

Cells do not contain any time
Below is a sample of the sheet, I am exporting this data into excel from SAP
so the layout is what I get.
I am lookin for a way to get a sum for column B,C amp; D for each day (ie:
2006/01/01)
There are many entries for each day.A B C D

2006/01/01 8 8 9
2006/01/01 8 9 9
2006/01/01 6 6 4
2006/01/02 5.5 5 5
2006/01/02 8 5 6
2006/01/02 9 5 6
2006/01/02 10 8 7
2006/01/03 7 6 7

quot;Dave Petersonquot; wrote:

gt; If you sort by column A, you can use Data|subtotals to add up those hours.
gt;
gt; If the hours are really cells containing time (5:00 or 7:30), then format the
gt; subtotals as [h]:mm.
gt;
gt; It'll avoid a problem if the subtotals exceed 24 hours.
gt;
gt; Or you may want to look into Data|pivottable.
gt;
gt; huffmjb wrote:
gt; gt;
gt; gt; Column A has dates eg: 2006/01/01, 2006/01/02 with many entries for each date.
gt; gt; Column B, C, D has data of hours used on these dates.
gt; gt; I am looking for a way to get the sum of each column specific to a date
gt; gt; (daily totals)
gt;
gt; --
gt;
gt; Dave Peterson
gt;


Hi huffmjb!

You have to break the SAP data up first before you can do what you are
wanting to do. Assuming SAP data starts in A1 try this (enter all in
row 1):

Col B
=SEARCH(quot; quot;,A1,12)
Col C
=SEARCH(quot;quot;,A1,SEARCH(quot; quot;,A1,12) 2)
Col D
=LEFT(A1,11)
Col E
=MID(A1,12,B1-12)
Col F
=MID(A1,B1 1,C1-B1-1)
Col G
=MID(A1,C1 1,LEN(A1)-C1)

Once this is entered, fill B1:G1 down to fit the SAP data length.
Columns D through G should separate into the date field, and three time
fields. Then use subtotal or sumif to pull specific dates. Columns B amp; C
are just to calculate where the packed spaces are located and can be
hidden.Neill--
neillcato
------------------------------------------------------------------------
neillcato's Profile: www.excelforum.com/member.php...oamp;userid=31750
View this thread: www.excelforum.com/showthread...hreadid=514557So just sort by the date and do data|subtotals. You'll see each day's total.

huffmjb wrote:
gt;
gt; Cells do not contain any time
gt; Below is a sample of the sheet, I am exporting this data into excel from SAP
gt; so the layout is what I get.
gt; I am lookin for a way to get a sum for column B,C amp; D for each day (ie:
gt; 2006/01/01)
gt; There are many entries for each day.
gt;
gt;
gt; A B C D
gt;
gt; 2006/01/01 8 8 9
gt; 2006/01/01 8 9 9
gt; 2006/01/01 6 6 4
gt; 2006/01/02 5.5 5 5
gt; 2006/01/02 8 5 6
gt; 2006/01/02 9 5 6
gt; 2006/01/02 10 8 7
gt; 2006/01/03 7 6 7
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; If you sort by column A, you can use Data|subtotals to add up those hours.
gt; gt;
gt; gt; If the hours are really cells containing time (5:00 or 7:30), then format the
gt; gt; subtotals as [h]:mm.
gt; gt;
gt; gt; It'll avoid a problem if the subtotals exceed 24 hours.
gt; gt;
gt; gt; Or you may want to look into Data|pivottable.
gt; gt;
gt; gt; huffmjb wrote:
gt; gt; gt;
gt; gt; gt; Column A has dates eg: 2006/01/01, 2006/01/02 with many entries for each date.
gt; gt; gt; Column B, C, D has data of hours used on these dates.
gt; gt; gt; I am looking for a way to get the sum of each column specific to a date
gt; gt; gt; (daily totals)
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson