A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how do i get the sum of data from specific dates



 
 
Thread Tools Display Modes
  #1  
Old February 20th, 2006, 11:06 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates

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)
  #2  
Old February 20th, 2006, 11:56 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates

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)

  #3  
Old February 20th, 2006, 11:57 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates

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
  #4  
Old February 21st, 2006, 05:26 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates

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

  #5  
Old February 21st, 2006, 08:39 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates


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


--
neillcato
------------------------------------------------------------------------
neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
View this thread: http://www.excelforum.com/showthread...hreadid=514557

  #6  
Old February 21st, 2006, 04:23 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: n/a
Default how do i get the sum of data from specific dates

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort pages? David General Discussion 15 May 13th, 2005 11:33 PM
strategy for data entry in multiple tables LAF Using Forms 18 April 25th, 2005 04:04 AM
SQL Comparing dates from form, Getting repeat data pdogg Running & Setting Up Queries 3 March 23rd, 2005 01:57 PM
Is this possible with Excel Chart? q582gmzhi Charts and Charting 1 September 8th, 2004 03:33 AM
How to create graphs in a monthly report where the base data can change John Clarke Charts and Charting 3 June 25th, 2004 02:22 AM


All times are GMT +1. The time now is 07:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.