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
I5:copy across and down.Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
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
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
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
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
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?
Too late for me just now, maybe tomorrow.
Sleep well
Thank youzzzzzzzzzzzzzzz
OK, without the Helper column:Formula:Please Login or Register to view this content.
or:Formula:Please Login or Register to view this content.
or:Formula:Please Login or Register to view this content.
you don't actually need the brackets around the $A$5:$A$23:Formula:Please Login or Register to view this content.
Regards, TMS
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!!
See the attached updated example.
Lose the white space at the top so that the Dynamic Named Range works.
Regards, TMS
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks