+ Reply to Thread
Results 1 to 13 of 13

Combined and summary

  1. 01-04-2010, 02:15 AM #1
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Combined and summary

    Dear Expert/Excel King

    i got problem on my excel worksheet here. Please refer my attached files.

    1. I want to link my sales detail worksheet to summary worksheet. When i insert an amount of 500 in "Sales details" worksheet on certain date then the amount will appear 500 and falls under the respective week and customer name also.

    2. When there is two or three delivery order at the same week, the amount that i type in sales details also will auto sum up and reflect in the "Summary" worksheet.

    Please help help urgent.. thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-04-2010, 02:27 AM #2
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Combined and summary!! Expert HELP!!

    Hi magic_ma,

    as often, the answer to this summing and categorising is a Pivot Table. It will do exactly what you need, i.e. summarize your data by time frame and customer, with just a few clicks and no complicated formulae. Get started reading and learning about pivot tables here: http://peltiertech.com/Excel/Pivots/pivotstart.htm

    hth
    Microsoft MVP
    http://www.teylyn.com
    Twitter: http://twitter.com/IngeborgNZ
    Reply With QuoteRegister To Reply
  3. 01-04-2010, 03:10 AM #3
    orlandom
    orlandom is offline
    Registered User
    Join Date
    05-21-2008
    MS-Off Ver
    2003/2007/2010
    Posts
    21

    Re: Combined and summary

    Hi magic_ma,

    Since you have a non consistent grouping scheme for dates you probably won't be able to use a pivot table.

    I don't know what version of MS Excel you're using, so I made two solutions for your problem; one with the function SUMIFS which is available in Excel 2007 and greater and another with SUMPRODUCT which will work in Excel 2003.

    I converted the range in sheet "Sales Details" to a Table (Ctrl+T) to make the formulas' references expandable.

    I hope this helps!

    Orlando Mezquita
    Attached Files Attached Files
    Last edited by orlandom; 01-04-2010 at 03:15 AM. Reason: Add details of my solution.
    Please read the Forum Rules:

    If your problem is solved, mark your thread as Solved. Read Forum's rule #10 to know how to do it.

    If you are happy with my answer please add it to my reputation by clicking in the icon next to the post number in my post.

    "Practice isn't the thing you do once you're good. It's the thing you do that makes you good."
    Malcolm Gladwell
    Reply With QuoteRegister To Reply
  4. 01-04-2010, 03:19 AM #4
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Combined and summary

    Since you have a non consistent grouping scheme for dates you probably won't be able to use a pivot table.
    I disagree. See attached ...

    Pivot tables will be more efficient and easier to customise than formulae.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-04-2010, 03:34 AM #5
    orlandom
    orlandom is offline
    Registered User
    Join Date
    05-21-2008
    MS-Off Ver
    2003/2007/2010
    Posts
    21

    Re: Combined and summary

    Pivot tables will be more efficient and easier to customise than formulae.
    I totally agree with you; Pivot tables is one of my favorite tools.

    However, the summary that magic_ma wants have one week of 9 days, two weeks of 7 days and one week of 8 days (non consistent grouping scheme) while yours have 4 weeks of 7 days and one with the remaining days (consistent grouping scheme).

    Orlando Mezquita
    Reply With QuoteRegister To Reply
  6. 01-04-2010, 03:41 AM #6
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Re: Combined and summary

    Hi All,

    Really thanks for all of your help..

    I will begin learn pivot tools... thank you so much...


    Thankszzzzzzzzzzzz
    Reply With QuoteRegister To Reply
  7. 01-04-2010, 03:54 AM #7
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Combined and summary

    magic_ma wants have one week of 9 days, two weeks of 7 days and one week of 8 days
    Well, maybe magic_ma will need to work on the understanding that a week has only 7 days, but that with the pivot table concept it's two to three clicks to sum up intervals of other sizes
    Reply With QuoteRegister To Reply
  8. 01-04-2010, 04:10 AM #8
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Re: Combined and summary

    Hi All,

    I still can't do it by Pivot table.

    Can someone sort it out for me? Many thanks
    Reply With QuoteRegister To Reply
  9. 01-04-2010, 04:16 AM #9
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Re: Combined and summary

    Hi Orlando Mezquita,

    Why summary sumif worksheet need to put date + 1?

    My summary worksheet only untill 2010-01-31 and can we change the first column to 1st week, 2nd week and 3rd and 4th week..

    Please help.
    Reply With QuoteRegister To Reply
  10. 01-04-2010, 04:38 AM #10
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Combined and summary

    Can someone sort it out for me?
    Sort out what? Could you be a bit more specific? The attachment in my earlier post had a working pivot table with the data summarized by week ....
    Reply With QuoteRegister To Reply
  11. 01-04-2010, 04:42 AM #11
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Re: Combined and summary

    Hi

    I have try myself to create a Pivot table.

    How can i set the column B4 in "Sheet 3" by giving name of 1st Week, Column C4 name with 2nd week, 3rd week and so on...


    And also the word sum of amount delete away?

    Please guide me...thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 01-04-2010, 04:54 AM #12
    teylyn
    teylyn is offline
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Combined and summary

    You need to delete the empty row 4 between the column headers and the data on sheet "Sales Details" then refresh the pivot table. Now right click on one of the pivot table dates and select Group. Select to group by days and enter an interval of 7 days.

    If you don't want to see "Sum of Amount" you can format it white text on white background.
    Reply With QuoteRegister To Reply
  13. 01-04-2010, 06:15 AM #13
    magic_ma
    magic_ma is offline
    Registered User
    Join Date
    06-16-2008
    Posts
    30

    Re: Combined and summary

    Hi Telyn

    Thanks for your help. But how can i make my sales details Cell B4 as 2010-01-01 to 2010-01-09, C4 with 01/10 - 01/16, D4 with 01/17 - 01/23 and E4 from 01/24 - 01/31.

    If i select interval to be 7 days, then each column will auto adjust to 7 days which i don't want as the 1st week i need define to 01/01 - 01/09.

    Please advice and your help is great appreciated.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1