Table summary

[Holger]

Hi everyone,

I have the following data, which is only a snapshot:

* col 1 and 2 are category and sub-category, col 3 to col 131 is periods from Oct 2023 to.....whatever
* what periods to summarise is determined by the two fields at the top Beginning and Ending, where someone can choose two periods in the range col 3-131. In the screen below I have only shown Dec 24, Jan 25, Feb 25
* I currently have long, complex formulas that create summary tables, which I further use for graphs.
* current formulas are many: unique to create the categories, then index/match to pick up the periods, and then sumproduct to populate these rows and columns

I'm sure there is a better way to get this done more efficiently. I managed to pull the respective columns with choosecols in a LET function but cant get the grouping down by category. I tried this LET thing before and got it working but that was more copying and adjusting with the help of someone from this forum. . I just don't get this thing completely .

I would like to achieve the small table below the row "desired result." The number of columns and subsequent calcs will depend on the range between beginning and end.

Test.xlsx
	GD	GE	GF	GG	GH	GI	GJ	GK
42	Select Beginning	Dec-24	17
43	Select End	Jan-25	18
44
45
46	Category	Sub	Dec-24	Jan-25	Feb-25
47	Housing	Rent	-8,780	-8,780	-8,780
48	Housing	Electricity	-118	-214	-337
49	Housing	Gas	0	-247	0
50	Housing	Water	-92	-227	-115
51	Housing	Comms	-346	-346	-346
52	Housing	Pay TV	-54	-54	-35
53	Housing	Insurance	-12	-12	-12
54	Food	Supermarket	-1,940	-2,410	-2,410
55	Food	Harris Farm	-353	0	0
56	Food	Other	0	0	0
57	Food	Coffee	-102	-102	-102
58	Food	Pet Food	-70	-257	-257
59	Food	Kmart	-113	0	0
60	Health	Health Insurance	-312	-811	-811
61	Health	Fitness	-227	-227	-227
62	Health	Medical	-328	-465	-200
63	Health	Pharmacy	0	-400	0
64	Health	Vet	0	0	-300
65	Education	School Fees	0	0	0
66	Education	BJJ	-410	-410	-410
67	Education	Muay Thai	-260	0	0
68	Education	Other	0	0	0
69	Car	Lease	-13,414	0	-2,267
70	Car	Fuel	-843	-771	-771
71	Car	Registration	0	0	-1,183
72	Car	Repairs	0	0	0
73	Car	Other	-20	-134	-1,200
74	…........
75
76	Desired Result
77		Category	Dec-24	Jan-25	Feb-25	Sum	Average	Percent of
78		Housing	-9,402	-9,880	-9,624	-28,907	-9,636	46%
79		Food	-2,578	-2,769	-2,769	-8,116	-2,705	13%
80		Health	-867	-1,903	-1,538	-4,308	-1,436	7%
81		Education	-670	-410	-410	-1,489	-496	2%
82		Car	-14,277	-905	-5,421	-20,603	-6,868	32%
83		Subtotal	-27,794	-15,866	-19,762	-63,422	-21,141	100%
CashFlow

Cell Formulas
Range	Formula
GF78:GH78	GF78	=SUM(GF47:GF53)
GI78:GI82	GI78	=SUM(GF78:GH78)
GJ78:GJ82	GJ78	=AVERAGE(GF78:GH78)
GK78:GK82	GK78	=PERCENTOF(GF78:GH78,$GF$78:$GH$82)
GF79:GH79	GF79	=SUM(GF54:GF59)
GF80:GH80	GF80	=SUM(GF60:GF64)
GF81:GH81	GF81	=SUM(GF65:GF68)
GF82:GH82	GF82	=SUM(GF69:GF73)
GF83:GK83	GF83	=SUM(GF78:GF82)

Thanks to everyone for any help.

Cheers H

 

[Fluff]

How about

Fluff.xlsm
	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN
42	Select Beginning	01/12/2024	17
43	Select End	01/02/2025	18
44
45
46	Category	Sub	01/09/2024	01/10/2024	01/11/2024	01/12/2024	01/01/2025	01/02/2025	01/03/2025	01/04/2025	01/05/2025
47	Housing	Rent	597	-55	799	-8,780	-8,780	-8,780	20	-40	419
48	Housing	Electricity	743	865	702	-118	-214	-337	275	78	211
49	Housing	Gas	23	227	684	0	-247	0	26	14	886
50	Housing	Water	402	534	698	-92	-227	-115	84	180	83
51	Housing	Comms	68	828	-5	-346	-346	-346	338	47	209
52	Housing	Pay TV	73	692	653	-54	-54	-35	160	969	10
53	Housing	Insurance	488	200	476	-12	-12	-12	-15	27	155
54	Food	Supermarket	402	317	441	-1,940	-2,410	-2,410	295	590	800
55	Food	Harris Farm	473	459	399	-353	0	0	854	795	784
56	Food	Other	246	1,000	418	0	0	0	955	654	928
57	Food	Coffee	291	691	819	-102	-102	-102	715	131	619
58	Food	Pet Food	801	-17	510	-70	-257	-257	852	977	187
59	Food	Kmart	-88	3	740	-113	0	0	751	498	753
60	Health	Health Insurance	174	-38	533	-312	-811	-811	720	877	458
61	Health	Fitness	-58	-9	26	-227	-227	-227	646	763	615
62	Health	Medical	841	733	64	-328	-465	-200	234	924	225
63	Health	Pharmacy	508	413	584	0	-400	0	101	149	-18
64	Health	Vet	-72	190	338	0	0	-300	557	-19	344
65	Education	School Fees	-57	993	936	0	0	0	733	452	182
66	Education	BJJ	837	514	651	-410	-410	-410	855	849	727
67	Education	Muay Thai	733	187	962	-260	0	0	749	321	785
68	Education	Other	612	265	675	0	0	0	717	884	832
69	Car	Lease	38	581	657	-13,414	0	-2,267	563	530	858
70	Car	Fuel	691	699	205	-843	-771	-771	917	-40	433
71	Car	Registration	750	109	965	0	0	-1,183	823	907	274
72	Car	Repairs	248	-26	353	0	0	0	894	728	18
73	Car	Other	331	74	203	-20	-134	-1,200	935	135	132
74	…........
75
76	Desired Result
77		Category	01/12/2024	01/01/2025	01/02/2025	Sum	Average	Percent of
78		Car	-14,277	-905	-5,421	-20,603	-6,868	32%
79		Education	-670	-410	-410	-1,489	-496	2%
80		Food	-2,578	-2,769	-2,769	-8,116	-2,705	13%
81		Health	-867	-1,903	-1,538	-4,308	-1,436	7%
82		Housing	-9,402	-9,880	-9,624	-28,907	-9,636	46%
83		Total	-27,794	-15,866	-19,762	-63,422	-21,141	100%
Data

Cell Formulas
Range	Formula
GE77:GK83	GE77	=LET(g,GROUPBY(GD46:GD73,FILTER(GF46:GN73,(GF46:GN46>=GE42)*(GF46:GN46<=GE43)),SUM,3),d,DROP(DROP(g,1),-1),s,BYROW(d,SUM),ss,SUM(s),a,BYROW(d,AVERAGE),p,MAP(s,LAMBDA(m,m/ss)),HSTACK(g,VSTACK("Sum",s,ss),VSTACK("Average",a,SUM(a)),VSTACK("Percent of",p,SUM(p))))
Dynamic array formulas.

 

[alansidman]

The grouping can be done using the GroupBy Functionality in Power Query.

 

[Holger]

@Fluff, thank you so much for your help. This works great but the problem I'm facing is, that if I adapt this to the real table I created, it always gives me #Value! or #Calc!.

My table is: A46:EA95 (first row header names)
Category to filter: A46:A95 (Tbl_expenses[ExpenseCategory]
Month: C46:EA46 (col A and B are Expenses Category and Expense Sub

=LET(
g,GROUPBY(Tbl_expenses[ExpenseCategory],FILTER(Tbl_expenses,(GF46:GN46>=GE42)*(GF46:GN46<=GE43)),SUM,3),
d,DROP(DROP(g,1),-1),
s,BYROW(d,SUM),
ss,SUM(s),
a,BYROW(d,AVERAGE),
p,MAP(s,LAMBDA(m,m/ss)),
HSTACK(g,VSTACK("Sum",s,ss),
VSTACK("Average",a,SUM(a)),
VSTACK("Percent of",p,SUM(p)))
)

In the above I have not yet changed (GF46:GN46>=GE42)*(GF46:GN46<=GE43)), as I already got an error message....

Thanks very any further help.

@alansidman, learning that will have to wait for another date, but thank you for outlining another approach.

 

[Holger]

@Fluff, I got it working. I made a mistake in defining the correct array. If I could ask one more questions please:
1. currently the data is sorted alphabetically by the category (col A). How do I adjust the function to sort from largest spend to lowest spend in the Sum or Percent of column?


Thank you so much.

 

[Fluff]

How about

=LET(g,GROUPBY(GD46:GD73,FILTER(GF46:GN73,(GF46:GN46>=GE42)*(GF46:GN46<=GE43)),SUM,3),d,DROP(DROP(g,1),-1),s,BYROW(d,SUM),ss,SUM(s),a,BYROW(d,AVERAGE),p,MAP(s,LAMBDA(m,m/ss)),SORTBY(HSTACK(g,VSTACK("Sum",s,ss),VSTACK("Average",a,SUM(a)),VSTACK("Percent of",p,SUM(p))),VSTACK(2,p,-1),-1))

 

[Holger]

How about

=LET(g,GROUPBY(GD46:GD73,FILTER(GF46:GN73,(GF46:GN46>=GE42)*(GF46:GN46<=GE43)),SUM,3),d,DROP(DROP(g,1),-1),s,BYROW(d,SUM),ss,SUM(s),a,BYROW(d,AVERAGE),p,MAP(s,LAMBDA(m,m/ss)),SORTBY(HSTACK(g,VSTACK("Sum",s,ss),VSTACK("Average",a,SUM(a)),VSTACK("Percent of",p,SUM(p))),VSTACK(2,p,-1),-1))

Awesome and thank you so much. There is still so much to learn.....

 

[Fluff]

You're welcome & thanks for the feedback.