Home

Display information by weeks & months on Pivot table

%3CLINGO-SUB%20id%3D%22lingo-sub-363675%22%20slang%3D%22en-US%22%3EDisplay%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363675%22%20slang%3D%22en-US%22%3EHi%2C%20last%20week%20I%20was%20trying%20to%20make%20a%20report%20that%20shows%20the%20sales%20evolution%20by%20weeks%20and%20months%20at%20the%20same%20time%20(example%3A%20January%3A%20week%201%2C%20week%202%2C%20week3%2C%20week%204%20-and%20the%20same%20for%20other%20months-).%20When%20researching%20I%20read%20that%20excel%20is%20not%20able%20to%20show%20the%20information%20that%20way.%20Is%20that%20true%3F%20Does%20somebody%20knows%20how%20to%20do%20this%3F%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-363675%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363691%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363691%22%20slang%3D%22en-US%22%3ESo%20that%20EXCEL%20can%20understand%20the%20period%20within%20which%20you%20want%20to%20return%20the%20Total%20Sales%2C%20you%20must%20specify%20the%20start%20date%20and%20end%20date%20of%20the%20week%20you%20are%20referring%20to.%20For%20example%2C%20if%20your%20Dates%20are%20in%20A2%3AA100%2C%20your%20Sales%20are%20in%20B2%3AB100%2C%20and%20you%20want%20to%20return%20the%20Total%20Sales%20for%20week%201%20of%20January%202019%2C%20you%20must%20specify%20the%20start%20date%20(1%2F1%2F2019%20in%20F2)%20and%20the%20end%20date%20(1%2F7%2F2019%20in%20G2)%2C%20so%20that%20your%20formula%20should%20look%20like%20this%3A%3CBR%20%2F%3E%3DSUMIFS(B2%3AB100%2CA2%3AA100%2C%22%26gt%3B%3D%22%26amp%3BF2%2CA2%3AA100%2C%22%26lt%3B%3D%22%26amp%3BG2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-363688%22%20slang%3D%22en-US%22%3ERe%3A%20Display%20information%20by%20weeks%20%26amp%3B%20months%20on%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-363688%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20need%20to%20add%20helper%20column%20first%20to%20your%20data%20which%20calculates%20weeknumber%20within%20the%20month.%20Let%20say%20firtst%20week%20of%20the%20month%20starts%20from%20first%20Monday%20and%20triggers%20to%201%20on%20the%20first%20Monday%20of%20next%20month.%20One%20of%20techniques%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Freset-week-number-every-month-excel%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xelplus.com%2Freset-week-number-every-month-excel%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreating%20the%20Pivot%20Table%20you%20add%20both%20Date%20and%20Week%20into%20rows%20pane%20and%20group%20Dates%20by%20months.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Carlos_Gomez
Frequent Visitor
Hi, last week I was trying to make a report that shows the sales evolution by weeks and months at the same time (example: January: week 1, week 2, week3, week 4 -and the same for other months-). When researching I read that excel is not able to show the information that way. Is that true? Does somebody knows how to do this?
2 Replies

Hi,

 

You need to add helper column first to your data which calculates weeknumber within the month. Let say firtst week of the month starts from first Monday and triggers to 1 on the first Monday of next month. One of techniques is here https://www.xelplus.com/reset-week-number-every-month-excel/

 

Creating the Pivot Table you add both Date and Week into rows pane and group Dates by months.

So that EXCEL can understand the period within which you want to return the Total Sales, you must specify the start date and end date of the week you are referring to. For example, if your Dates are in A2:A100, your Sales are in B2:B100, and you want to return the Total Sales for week 1 of January 2019, you must specify the start date (1/1/2019 in F2) and the end date (1/7/2019 in G2), so that your formula should look like this:
=SUMIFS(B2:B100,A2:A100,">="&F2,A2:A100,"<="&G2)