how do i get the sum of data from specific dates

[huffmjb]

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)

[Enron]

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

> 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)

[Dave Peterson]

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:
>
> 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)

--

Dave Peterson

[huffmjb]

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 & 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

"Dave Peterson" wrote:

> 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:
> >
> > 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)
>
> --
>
> Dave Peterson
>

[neillcato]

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(" ",A1,12)
Col C
=SEARCH("",A1,SEARCH(" ",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 & C are just to calculate where the packed spaces are located and can be hidden.


Neill

[Dave Peterson]

So just sort by the date and do data|subtotals. You'll see each day's total.

huffmjb wrote:
>
> 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 & 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
>
> "Dave Peterson" wrote:
>
> > 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:
> > >
> > > 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)
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson