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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |