how do i get the sum of data from specific dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
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 said:
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.
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)
Click to expand...
Click to expand...
 
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
 
So just sort by the date and do data|subtotals. You'll see each day's total.
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 said:
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.
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)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top