Aggregating Monthly Sales from a Larger Data Set

Tonya has a worksheet that contains sales transactions for the past two years. The data is imported daily from an external reporting system. The data is simple; column A is the date and column B is the amount. On a different worksheet, Tonya would like to create a small table that represents aggregate sales for just the past six calendar months. Column A would be the month, column B would be the count of sales, and column C the sum of sales. She knows she could do this with a PivotTable, but she would prefer to do it using formulas, if possible. That way she can include the aggregate data in a larger report that she is putting together.

I'm going to make an assumption here that Tonya's transaction data is in a worksheet called "Import," using columns A:B as she indicated. In order to get the information she wants, on a different worksheet she can add the headings "Month," "Count," and "Sales" in cells A1:C1. Then, she can use the following formula in cell A2 in order to indicate the start of the month:

=EOMONTH(TODAY(),SEQUENCE(6,,-1,-1))+1

This will spill down in A2:A7, providing the starting date of each of the previous six months. Next, the count of transactions during the month can be determined with this formula in cell B2:

=LET(a,Import!A:A,ROWS(FILTER(a,(a >= A2) * (a < EDATE(A2,1)))))

Finally, the sum of those transactions can be determined with the following formula in cell C2:

=SUM(FILTER(Import!B:B, (Import!A:A >= A2) * (Import!A:A < EDATE(A2,1))))

Now, you can copy the formulas in B2:C2 down to rows 3 through 7 and you end up with the six months of aggregate data that Tonya is seeking.

The formulas presented so far use functions that will only work in Excel 2021, 2024, and Microsoft 365. If you want formulas that will work in older versions of Excel, place the following into cell A2, B2, and C2, respectively:

=EOMONTH(TODAY(),-ROW(A1))+1
=COUNTIFS(Import!A:A,">=" & A2,Import!A:A,"<" & EDATE(A2,1))
=SUMIFS(INDEX(Import!B:B,0),INDEX(Import!A:A,0), ">=" & A2,INDEX(Import!A:A,0), "<" & EDATE(A2,1))

Copy A2:C2 down to rows 3 through 7, and you have the information required.

Note that all of the formulas in this tip work on the entirety Import!A:B. If you notice that the calculations are slow at all, you can modify the ranges to reflect just the cells you want checked, as in A2:A5000 instead of A:A.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9703) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.