[Excel 2010] Daily summary from date+time values
Hello Everyone,
I have a sheet1 with date+time and values:
2010/07/12 04:39:50 ; 100
2010/07/12 14:52:10 ; -10
2010/07/13 01:56:34 ; 100
2010/07/13 12:41:02 ; 50
etc.
On another sheet2, I am attempting to make a "daily summary" using a SUMIF
2010/07/12 ; =SUMIF((sheet1!A:A),A1,sheet1!B:B) //result should be 90
2010/07/13 ; =SUMIF((sheet1!A:A),A2,sheet1!B:B) //result should be 150
etc.
The issue is, sheet1 features date+time and sheet2 only the date (it seemsthe time value is 00:00:00 by default), and the SUMIF() returns 0.
I have tried many workarounds, but none worked. Do you have any idea on how to make this work?
Many thanks,
Last edited by duration; 08-02-2010 at 02:25 AM. Reason: Wrong title
Is the 100 and - 10 in a seperate cell? What does it represent.
Hello Flebber,
Thank you for your reply.
Yes, the 100, -10 etc. values are in a separate cell.
I have separated cells with a semi-column to write in the forum.
They are account transactions.
I have added a few pictures if this is unclear.
What would be more useful would be a workbook with dummy data in.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
Maybe
Enter with ctrl/shift/enter.=SUM(IF(INT(sheet1!A:A)=A1,sheet1!B:B))
I'm not sure, but it might me more efficient if you refer to a limitid range instead of to the entire columns.
Hello RoyUK,
Thank you for your reply. Here you is the book.
Many thanks,
Hello Estige,
Sorry it does not work, with or without array.
Many thanks,
I think the most efficient way would be a PivotTable, with the dates Grouped by Days
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
royUK is probably right, but here is your workbook with my suggestion. It seems to work just fine to me?
I see you have Excel 2010, you could use SUMIFS:
2010/07/12; =SUMIFS(Sheet1!B:B, Sheet1!A:A, ">="&Sheet2!A1, Sheet1!A:A, "<"&A2)
2010/07/13; =SUMIFS(Sheet1!B:B, Sheet1!A:A, ">="&Sheet2!A2, Sheet1!A:A, "<"&A3)
2010/07/14;
etc...
I think the Pivottable would be better than Sumifs. Here's an example for 2007.
Note: the data is set as Table, now as you add data to the table you can open the Summary sheet and select a cell in the PivotTable and click Refresh in the Data group on the Options tab of the ribbon.
Hope that helps.
RoyUK
--------
If you are pleased with a member's answer then use the Star icon to rate it, if you are pleased enough to part with cash consider a donation to Children in Need
For Excel Tips & Solutions, free examples and tutorials why not check out my downloads
New members please read & follow the Forum Rules
Remember to mark your questions Solved and rate the answer(s)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks