Sometimes it’s important to collect and know how many sales you’ve made in the past, for example, in the last 30 days. Using this Excel formula will allow you to do that. Here’s how to sum the last 30 days sales in Excel.
Why you need monthly sales data
Some business owner wants to gather sales report in daily, weekly, monthly, or yearly basis. Sometimes, they also need 30 days interval to gather the last 30 days of sales data counting from today.
It’s good to gather these kinds of data. It will improve your business sales performance from month to month consistently in real-time. While monthly data that counts from the first date of the day to the end of the month isn’t really reliable at some point.
In Microsoft Excel, you can actually show and sum the last 30 days sales counting from today automatically even when the data is always updated on a daily basis. Doing this allows you to gather other specific data like the average sales on a daily basis from the last 30 days.
How to sum the last 30 days sales in Excel
Summing the last 30 days sales is easy. Simply prepare your Excel sheet containing a full list of dates and the sales number for that date. You can sum multiple same dates and still get the accurate calculation to know the last 30 days sales in Excel.
On the right, prepare a cell that will contain the sales in the last 30 days. You can put it on the right side like this example.
On that cell, put this formula:
=SUMIF(date range,”>=”&TODAY()-30, sum range)
- Date range: criteria range which will be the date
- Sum range: the value range you want to sum
Choose the range accurately based on the column’s criteria or value range. You can also create a table for the data so you don’t have to manually add a row to be calculated every time you want to add a new sale. However, in this example, I choose the range manually.
My formula would be like this:
It is showing sales in the last 30 days.
The Formula Explained
This Excel formula works by using the SUMIF function to sum based on a criteria. Basically, you can choose one value range with one criteria range.
- B2:B33 is the criteria range, which is all the rows containing dates.
- C2:C33 is the value range, the rows you want to sum.
- “>=”&TODAY()-30 is the criteria, which filter row that contains the last 30 days’ dates to be calculated.
- “>=” means we’re summing any value that is equal or greater than TODAY()-30
- TODAY()-30 is today’s date
- 30 is the total days of the month. We use that number to substract today’s date, so we can gather data 30 days before today’s.
You can also show a weekly (like the last 7 days) sales by changing the “30” in the formula to be 7, or any day count you want.
How to sum the last 30 days sales in different categories
You can get the data on the last days sales according to the categories. For example, I have a list of clients ordering my digital services and some ordered repeatedly. To know how many sales done for each costumer, you can use this formula instead:
=SUMIFS(value range,date range,”>=”&TODAY()-30,costumer’s name range,the costumer’s name)
This allows you to understand your data better depending on each costumer, and you may improve sales as well. The formula works the same way just like the previous one, with addition of the costumer’s name range and the costumer’s name as the criteria, which can be done by using SUMIFS function.
Finally, you can now know more about your business performance in the last 30 days, in the last 7 days, or for example, in the last 90 days.
You can modify the formula to match your needs, and you can also make the sheet containing the value, range, and else to be in a table so you can always add a new sale without manually change the formula to sum it.