Hi - Dave here.
Happy Friday!
The GROUPBY function makes generating monthly totals pretty easy. In the worksheet below, the formula in cell G5 looks like this:
=GROUPBY(TEXT(data[Date],"mmm"),data[Total],SUM)
This formula works nicely. You can see that we get a clean breakdown by month for the first six months of 2025. However, there is a problem. Notice that the month names do not appear in chronological order. Instead, they are sorted alphabetically. This is because the GROUPBY function sorts grouped values in
alphabetical order by default:
[
Download the workbook and read the full explanation]
How can we get Excel to sort the months in the correct order? This turns out to be tricky. However, it's a nice example of a problem where the
field_relationship argument in GROUPBY is needed. Click the link above for a full teardown and explanation.
Note: GROUPBY requires Excel 365. However, I've included a SUMPRODUCT solution on the second sheet that will work in any Excel version. Is the SUMPRODUCT formula simpler? Yes, maybe. You be the judge 🙂
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.