For example:
=GROUPBY(Table1[Invoice Date]-DAY(Table1[Invoice Date])+1, Table1[Amount], SUM, 0, 1)
where Table1 is the name of your table.
GROUPBY using months for rows when source dates are every day of the month
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(182.40000000000003, 7.000000000000001%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMM%3C/text%3E%3C/svg%3E)
Michael Morris
0
Reputation points
Hi,
I receive Sales and Invoice summaries in an Excel table. I would like to summarize the results by the month of the Sale or Invoice using GROUPBY and PIVOTBY.
The source table presents the date of each Sale or Invoice on the day it occurred so to use GROUPBY and PIVOT by I must create a new EOMONTH column for each Sale and Invoice event that GROUPBY and PIVOTBY use to summarize by month.
Is there a way to group rows by the Month and Year of each Sale and Invoice transaction without creating a helper column?
My source table before I add the helper column is:
The output of the above table using GROUPBY should be like below (or any other date format so long as months are summarized):
Look forward to your help.
Mike
Microsoft 365 and Office | Excel | For business | Windows
Accepted answer
-
HansV 460.8K Reputation points MVP Volunteer Moderator2025-08-12T18:53:46.2766667+00:00 -
Michael Morris 0 Reputation points
2025-08-13T12:22:40.08+00:00 Thanks Hans, and this is creatively simple.
However I was not expecting this to work because I had originally tried using EOMONTH to modify the Rows, but GROUPBY would not accept that. So I assumed the Rows input for GROUPBY could not include a formula, yet the subtraction function is acceptable.
In other words, this doesn't work:
=GROUPBY(EOMONTH(Table1[Invoice Date],0),Table1[Amount],SUM,0,1)
If you are able to briefly explain why subtraction works but a function like EOMONTH doesn't I would appreciate the eductation.
Thanks so much...Mike
-
HansV 460.8K Reputation points MVP Volunteer Moderator
2025-08-13T13:49:15.16+00:00 Although most functions are now dynamic array-capable, EOMONTH is not, at least not "as is". There is a simple trick to make it work: insert a + before the first argument to turn it into an array:
=GROUPBY(EOMONTH(+Table1[Invoice Date], 0), Table1[Amount], SUM, 0, 1)
-
Michael Morris 0 Reputation points
2025-08-13T13:51:56.4666667+00:00 Hans,
This detail is both useful and appreciated.
Mike
Sign in to comment -
1 additional answer
Sort by: Most helpful
-
Ashish Mathur 99,580 Reputation points Volunteer Moderator2025-08-12T22:58:39.93+00:00 Hi,
In cell E2, enter this formula
=LET(id,A2:A7,DROP(GROUPBY(HSTACK(YEAR(id)&TEXT(id,"mm"),TEXT(id,"mmm-yy")),C2:C7,SUM),,1))
Hope this helps.