Customers can make multiple purchases per month. What is the maximum amount that a customer has spent in a single month? This was a video from the amazing Mike Girvin (Excelisfun). The question is clear, but how did Mike solve it? How would you solve it?

What Does the Data Look Like?

We have 3 fields and 3656 rows of data.

Look carefully at the data below. Can you see what the challenge is?

For each ‘CustomerID’ we need to add up the ‘Sales’ amount per month. But, we have data at the day level not the month level. We need to aggregate it up to the month level.

Get The Excel File here and follow along!

Mike’s Solution: Pivot Table And Max function

This is my favorite solution. Why? It’s so simple and quick!

Create a Pivot Table with ‘Customer ID’ in ‘Rows’, ‘Date’ in columns and ‘Sales’ in ‘Values’ area. Note that you might have to group ‘Date’ by month. We now have the monthly aggregated total for each customer. Then add a MAX function to get the largest aggregated amount. 

It’s almost perfect but the pivot would need to be refreshed if you add or change data and you might have to adjust the range of the MAX function if your Pivot Table expands.

Mike’s Solution: Array Formula

Here is Mike’s array (note that AGGREGATE function can handle arrays without control shift enter).

=AGGREGATE(14,4,SUMIFS(F8:F3663,E8:E3663,A8:A88,D8:D3663,

“>=”&EDATE(B3,{0,1,2,3,4,5,6,7,8,9,10,11}),D8:D3663,

“<=”&EDATE(EOMONTH(B3,0),{0,1,2,3,4,5,6,7,8,9,10,11})),1)

Why such a complex formula? If you don’t like the pivot table taking up space in your sheet and you want a single cell solution then all the steps have to be done inside of a single formula. Yes, this solution is dynamic (the Pivot Table solution requires a refresh if data changes) but is it really worth it?

For me, I love arrays, but this formula is definitely a stretch. Nevertheless, I like it because I want to learn more about the versatile and powerful AGGREGATE function. I haven’t yet become comfortable enough with the AGGREGATE function to be able to quickly use it.

My Solution: Helper Columns

I was thinking “Is there possibly an easier to understand multiple step dynamic solution?” You’ll find my solution in sheet ‘Kevins Helper Solution’.

Solution Steps

1. Column Q helper truncates ‘Date’ values to start of month (i.e. 2016-07-28 becomes  2016-07-01)
2. Columns C to N calc monthly total per ‘CustomerID’. Formulas in row 6 dynamically create start of month values. Sumifs starting in row 8 calculate ‘Sales’ per ‘Month’ and ‘Customer ID’
3. Now that we have all the monthly totals per ‘CustomerID’ the simple MAX function in cell T5 calculates the final answer!

Yes, this method requires a lot of cells for the formulas but some may prefer this as it’s easier to audit compared to the array solution. However, if we had to do this for every month over many years (not just 1 year as we have here) then we would need a ton of helper columns and it would probably not be a good solution.

Compare Solutions

9 times out of 10 Mike’s Pivot Table and Max formula should win! Super quick and easy to explain. The one formula array solution is for Excel power user’s and my helper column solution would work for non power users that want a dynamic solution that they can audit and understand.

I’ve created this matrix to compare the solutions based on several factors. Note that there are comments in most cells that you can see if you download my Excel file.

YouTube Videos

Mike’s video, my video.

What solution do you prefer and why? Can you think of another solution?

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’m a Data Analyst. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001.

There are so many amazing things that you can do with this powerful software. Check out my videos and my blog posts.

Away from Excel I enjoy learning Spanish, playing Chess, hanging out with Cali and Fenton and reading Excel books 🙂