Sign in or start a free trial to avail of this feature.
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.
Finding the Month from a Date (00:04)
We want to add a Sales by Month section to the search panel, allowing us to select a month and see customers and sales for that month. To do this, we need to get the month from our date field.
We could do this using the MONTH function. This takes a date as its argument and returns the month number for that date, i.e. 1 for January, 2 for February etc. However, this won’t return the name of the month. To get the month name, we use the TEXT function, using the format string “mmmm” to return the full month name.
Creating the Month Dropdown (01:19)
To create the month dropdown, we first create a list of all the possible months. Then, n the search panel, we select the cell that will contain the dropdown, and open Data Validation. We set the list of months as the possible values for the cell. This creates the dropdown for each of the months. We also make sure to name this cell, so we can use it more easily in formulas.
Calculating Customers and Sales by Month (02:00)
Once we’ve created a working dropdown, we can create two array formulas to find the number of customers and the total revenue for the selected month.
To calculate the number of customers for the selected month, we use an IF statement inside a COUNT function. The logical test for the IF function checks the dataset to find dates from the current month. It does this using the TEXT function. We return an array of 1s for each date from the selected month. COUNT then counts these ones to return the number of customers for the selected month.
Calculating total sales for the selected month is very similar. Instead of a series of 1s, we return the revenue figures for the appropriate dates. Instead of a COUNT function, we use a SUM function to find the total revenue.
In the previous lesson, we learned how to create a lookup containing multiple criteria. In this lesson, we'll learn how to calculate monthly sales totals on our search panel. 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 data into the month function, it returns a number between one and 12 representing the month from January to December. So I'll type equals month and then select the date, close the bracket and press Enter. And this returns one 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 the string of text to the function. So I'll write equals text, open the bracket and then select our days.
And then in the format I'll open inverted commas, and write four Ms, which represents the full month, and then close the bracket. And then press Enter. And this now returns January from our days. 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 create a new list for each month.
So I'll type January, and then drag the bottom right hand corner to autofill 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 named 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 counter 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 data array, and the formats will be the four Ms that we used earlier.
And then we'll check if this is equal to the selected month.
Then comma and we'd return one if it does, and we'd return nothing if it doesn't.
We then close the brackets, press Control + Shift + Enter to perform the array formula.
This formula creates an array of ones 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 right equals some this time, and then the same if statement, where we'll test if the text format of our date for the four Ms is equal to the selected month.
And if it is, this time we'll return the revenue value.
And if it isn't, will return nothing.
And then close the bracket and Control + Shift + Enter, 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.
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 to calculate a similar table for days of the week. The text form for days is four DS, which you can use instead of the four Ms we used for months.