8. Calculating Monthly Totals
Create a search panel that allows you to pick a month from a dropdown list and returns the total revenue for that particular month.
Aggregating transactions into monthly totals
- To extract the name of the month from a date, we use the TEXT function
- We can choose 'mmm' for 3-letter form or 'mmmm' for full spelling of the month
- Replacing 'mmm' with 'ddd' returns the equivalent day of the week
Autofill months: Drag bottom right-hand corner of cell
ALT + A, V, V: Open Data Validation dialog box
CTRL + F3: Open the Name Manager dialog box
ALT + N: Create a new Name (only within Name Manager)
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
CTRL + →: Move to the last cell in the data region
CTRL + SHIFT + ENTER: Create array formula
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
In this lesson, I'd like to add a Sales by Month section to our search panel, where I select a month and I can immediately access the number of customers and the total sales for that month.
Unfortunately, we don't currently have a month column in our Excel data set, so how do we do this? One way you might consider is using the MONTH function.
When I pass a date into the MONTH function, it returns a number between 1 and 12 representing the months from January to December.
So I will type “=month”, I'll then select the date, close the bracket and press Enter, and this returns 1 for January.
However, I'd like the name of the month rather than just this number.
So instead we’ll use the TEXT function.
The TEXT function changes the output format of the cell by passing a string of text to the function.
So I'll write “=text”, open the bracket and then select our date.
And then in the format, I'll open inverted commas and write four M's, which represents the full month and then close the bracket.
I'll then press Enter, and this now returns January from our date.
We can now use the TEXT function to perform monthly lookups on our data set.
First we'll move to our sheet where we create lists.
And we’ll create a new list for each month.
So I'll type January and then drag the bottom right hand corner to auto fill for the remaining months.
We'll now go back to our search panel and create data validation for this list.
So Alt + A V V, we'll select List and then we’ll select those months that we just created.
Press OK to finish.
And now we have a drop down for each of our months.
Let’s name this cell SELECTED_MONTH, and start by calculating the number of customers.
We'll do this by using the COUNT function, and inside the COUNT function we’ll create an IF statement.
I'm not going to use the inbuilt COUNT IF function in Excel for this task, because we need to use array formulas and the inbuilt function makes this a little tricky.
So within the IF statement, the logical test will be if the month format of our date column is equal to the selected month, which in this case is July.
So in the TEXT function, we'll select the date array, and the format will be the four M's that we used earlier.
And then we'll check if this is equal to the selected month, then comma, and we’ll return 1 if it does, and we’ll return nothing if it doesn't.
We'll then close the brackets, press Ctrl + Shift + Enter to perform the array formula.
This formula creates an array of 1’s, each corresponding to a transaction entry where the month is July.
It then performs a COUNT function on this array and returns 14 transactions.
Let's now calculate the total sales for each month.
So I'll write “=sum” this time, and then the same IF statement, where we’ll test if the text format of our date for the four M's is equal to the selected month.
And if it is, this time we'll return the revenue value.
And if it isn't, we'll return nothing.
We'll then close the bracket, and Ctrl + Shift + Enter to finish.
And this now gives us the total sales value for every month we want.
We can check our calculations by going to the data set and selecting all of the revenue values for January.
So I'll skip up to the top and I'll just select all of the January values.
And in the footer, we can see that the sum value is equal to our total sales, and that count is equal to our total number of customers.
To practice using the TEXT function, try to calculate a similar table for days of the week.
The text form for days is four D's, which you can use instead of the four M's we used for months.