Excel: How to Sum Data by Week, Month and Year【Data Analysis Excel Skill】
Excel Date Aggregation: We need to sum data by week, month or year frequently. The easiest way is to use a work column and the SUMIFS function.
(Duration: 9:29)
＜＜ Related Post ＞＞
- How to learn Excel functions, and a look at Excel functions: SUM, SUMIF and SUMIFS【Excel Skills】
- Excel Absolute and Relative Cell References and Autofill Function【Data Analysis Excel Skill】
The 1st Step of Data Analysis is to Decide Final Layout of your Result.
Hi, this is Mike Negami, Lean Sigma Black Belt.
Today’s topic was requested by one of my viewers.
“I really need to know how to sum data by week/month/quarter/year in Excel. Any tips?”
As with any analysis, the first thing to know is what the person who will see your analysis wants to do with the result and why. Ask the person directly about those questions. Then consider and decide layouts for your result. As an example, let’s sum weekly sales by sales channel for the past 6 weeks.
Let’s design a final layout first. I’ll put the sales channels in Column I and weekly sales between Columns J and O. In this exercise, we’ll use the SUMIFS Function that we learned in an earlier video.
＜＜ Related Post ＞＞
The table headers are in Row 2. Since we want to use the headers as conditions of the SUMIFS Function, their formats are important. I would like to put a header representing last week in Cell O2 (See the image above.). How can we do that? By searching for on Google, you can see that there is a WEEKNUM Function. If you type [ =WEEKNUM ] in a cell, it says “Returns the week number in the year”.
Put the 1st parenthesis, then it shows its argument is ‘serial_number’. For now, consider that ‘serial_number’ is a date. Today is October 19th, so last week is October 12th. Since this format is not numbers, but text, you need double quotations on both sides.
Then it came out with 41. In other words, last week is the 41st week in this year. I’ll manually enter the previous 5 weeks for now.
Output your target date format in a work column.
Next, let’s see the source data. There are ‘Sales Date’ in Column A, ‘Sales Channel’ in Column B and ‘Sales Amount’ in Column D. Using Column E as a work column, I’ll convert each sale date to a week number with the WEEKNUM Function. I’ll type and select the sales date, close parenthesis and double-click on the right-lower corner of the cell to copy it to the bottom.
It seems that using these items, we can calculate weekly sales with the SUMIFS Function, but there is one problem. When the source data has accumulated and it covers more than one year, we would sum up sales for the same week numbers in all the years. Therefore, it’s better to include the distinction of years in this work column. You could use the TEXT Function we learned in the previous video, but if you just need years, you can get them with the YEAR Function.
＜＜ Related Post ＞＞
Before the WEEKNUM Function, put [ YEAR( ] and select the sales date as ‘serial_number’ and close the parenthesis, which would show year. Then you can connect it and the week number with an [ & ] symbol, but they look like a continuous number, so it’s hard to read. Therefore, I’ll insert [ ” W”& ] between and you’ll get results like this:
Since this format will be reflected in the final result table, you should decide the format with that in mind. Copy the cell to the bottom. The preparation of the source data is now complete. Let’s go back to the final result table.
Complete the Horizontal Axis and Vertical Axis of the Final Result.
I need to put all sales channels in Column I. How shall we do this? Copy the column of the sales channel in the source data and temporarily paste that here. Then, click ‘Data’ and select ‘Remove Duplicates’ and click ‘OK’. You just extracted all sales channels in a flash.
In fact, there are only two. Copy them to the result table. This ‘Remove Duplicates’ feature is very useful. I use it quite often, but it’s not well known, is it? Please try it out.
Since we put years in the work column, we need to change the format of the headers. You can manually add a year to them, but if you do that, you have to manually change them every week as new data is added. I want them to change automatically by using formulas. Let me show you how.
I copied the formula in the work column and pasted it in the header. Now It’s referring to a blank. Put [ TODAY() ] (See the image below.), which always refers to today’s date, in the cell reference part. I’ll do the same for the argument of the WEEKNUM Function and press my enter key. It looks good.
Copy the cell to all the headers, then we’ll get all the same results. Here I subtract 7 days, then it becomes last week, subtract 14 days then it becomes the week before. In the same way, subtract 21, 28, 35 and 42, then they all become preceding week numbers.
Complete the Final Result Using the SUMIFS Function.
For the ‘Total’, just sum these two cells. It’s easy. Next is the last formula. Let’s complete the SUMIFS Function for the ‘Sales Channel’. Type [ =SUMIFS( ]. For ‘sum_range’, I’ll select ‘Sales Amount’. Since I’ll copy this cell horizontally and vertically later, we have to consider Absolute and Relative Cell References that we learned about in the last video. If you haven’t watched that video, since we’ll always need those contents from now on, please be sure to watch it.
＜＜ Related Post ＞＞
Since I don’t want this reference shifted, I’ll press my ‘F4’ key because I use Windows, in order to put $ symbols, which become Absolute References (Press ‘Command’ + ‘T’ for Mac). Then I’ll enter a comma. For ‘criteria_range1’, select Column E, which is Week Number. Since I want this reference fixed, I’ll change it to an Absolute Reference.
I’ll put a comma and ‘criteria1’ will be the header above. Please pay close attention here. When copying the cell to the right, I want the reference point to shift right, but when copying it down, I want it to be fixed. Therefore, I’ll put a $ symbol only in front of the row number.
For the next ‘criteria_range2’, I’ll select Sales Channel in Column B. I also want this fixed, so I’ll put a $ symbol and a comma. ‘criteri2’ is ‘Sales Channel’. When copying the cell to the right, I want this reference to be fixed, but when copying it down, I want it to shift down. Therefore, I’ll put a $ symbol in front of the column letter and close the parenthesis, then it’s completed.
Even when I copy it vertically and horizontally, it calculates properly. Lastly, I’ll make the table look a little better, then complete it.
If this is done properly, you can do the same thing for monthly, quarterly, and yearly aggregations. Make each result appear in the work column and change the header of the result table accordingly.
