+ Reply to Thread
Results 1 to 13 of 13

Need to summarize transactio values by week

  1. #1
    Registered User
    Join Date
    06-02-2009
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    60

    Need to summarize transactio values by week

    I need to generate a summary table for transactions based on week. Each transaction is recorded by "full date". I created a column to calculate the week number in table 1 (if we can disregard it that is fine) to match the anticipated reporting format in the header of table 2. I need to populate table 2 by summing all values for each category by week.
    I appreciate VBA code or formula. Attached is the file contains example .. Thanks
    Wee.xlsx

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    I5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copy across and down.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,980

    Re: Need to summarize transactio values by week

    Here is 1 way...
    =SUMIFS($F$5:$F$23,$E$5:$E$23,$H5,$B$5:$B$23,I$4)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,980

    Re: Need to summarize transactio values by week

    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

    Your profile states 2003, but your file is .xlsx - 2007 or later

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    Can't say I noticed the profile. I just prefer SUMPRODUCT. I think it's more versatile than SUMIFS and easier to use with dates, times and parts of dates and times. So, I tend to default to SUMPRODUCT rather than SUMIFS. Probably more to do with what I learnt first ... and old dogs, new tricks

    Regards, TMS

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,980

    Re: Need to summarize transactio values by week

    I am justteh opposite, but am trying to learn more about SP's versatility, so I also try and use it more.

    I did notice it looks like the OP also asked if they could do away with the weeknum helper column. I messed with that a bit with SP, but couldnt get it?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    Too late for me just now, maybe tomorrow.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,980

    Re: Need to summarize transactio values by week

    Sleep well

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    Thank you zzzzzzzzzzzzzzz

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    OK, without the Helper column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    you don't actually need the brackets around the $A$5:$A$23:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS

  11. #11
    Registered User
    Join Date
    06-02-2009
    Location
    Toronto
    MS-Off Ver
    Excel 2010
    Posts
    60

    Re: Need to summarize transactio values by week

    Thanks FDibbins & TMS for the great help .. all solution work perfectly!
    Can I add one more thing? Can I make the range dynamic (i.e. not limited to row 23), but work for any table size in case I expand on the data?

    Thanks again!!

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    See the attached updated example.

    Lose the white space at the top so that the Dynamic Named Range works.

    Regards, TMS
    Attached Files Attached Files

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,756

    Re: Need to summarize transactio values by week

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Best way to Summarize Table Quarter, week & State
    By shiva_reshs in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-26-2013, 06:36 AM
  2. Is there an excel function summarize data according to Day of week?
    By Eaglekirk in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-23-2013, 07:02 PM
  3. summarize values between 2 years
    By silviario in forum Excel General
    Replies: 1
    Last Post: 04-15-2011, 10:47 AM
  4. Summarize date values
    By gnoke in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-10-2010, 09:39 AM
  5. How do I summarize values?
    By BKolb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-21-2008, 11:32 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1