use query or similar to get categories by month

[ShadowRider]

I currently use
=query('2023'!E2:G, "select G, sum(E) where G is not null group by G")
to get a list of categories and their sums but I'd now like to get a sub total by month.
Column A in the sheet has the date, G is category and E is amount.

I could sort by month and create separate sheets for each month but think there should be an earlier solution.

Thanks!

 

[awoohaw]

What version of excel is this? I have insiders beta edition, and I do not have the QUERY function.
Please update your profile so your version shows up on your profile "button"?

But, you can do this with a pivot table:

Mr Excel Questions 73.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Date	colb	colc	cod	Amt	colf	Category
2	2023-09-12				11		D		Sum Amt	Mon
3	2023-08-12				24		C		Category	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
4	2023-09-12				22		C		A			18	24		36	20		24		21
5	2023-01-08				22		E		B	29	12	23		21		27	12
6	2023-12-10				15		C		C		13	15	37	28		25	24	22	35	53	15
7	2023-10-12				11		C		D		18	46	35		25	16		11			24
8	2023-03-14				21		D		E	22		35	16		19	10		22		16
9	2023-08-09				12		B
10	2023-06-11				14		A
11	2023-07-09				20		A
12	2023-07-10				16		B
13	2023-11-16				16		C
14	2023-11-09				10		A
15	2023-04-12				10		A
16	2023-03-13				15		C
17	2023-04-11				12		D
18	2023-09-16				22		E
19	2023-10-08				24		C
20	2023-11-11				11		A
21	2023-07-16				16		D
22	2023-11-11				24		C
23	2023-06-13				22		A
24	2023-04-11				23		D
25	2023-05-13				15		C
26	2023-05-10				13		C
27	2023-05-15				21		B
28	2023-07-13				25		C
29	2023-12-08				24		D
30	2023-03-13				11		E
31	2023-06-10				19		E
32	2023-04-08				24		C
33	2023-03-13				24		E
34	2023-02-09				12		B
35	2023-11-12				16		E
36	2023-02-09				13		C
37	2023-03-08				23		B
38	2023-01-10				16		B
39	2023-01-09				13		B
40	2023-03-10				25		D
41	2023-04-09				14		A
42	2023-06-16				25		D
43	2023-09-10				10		A
44	2023-04-09				16		E
45	2023-02-14				18		D
46	2023-07-11				10		E
47	2023-09-09				14		A
48	2023-04-08				13		C
49	2023-11-09				13		C
50	2023-03-09				18		A
51	2023-07-08				11		B
Sheet3

 

[ShadowRider]

I must admit that I was not using excel but passing along a question my daughter asked me (she is apparently using google sheets). I have forwarded your answer and recommended she use a pivot table there as well.

As for me, I'm using excel for MAC version16. (386 subscription)

-Eric

 

[awoohaw]

I must admit that I was not using excel but passing along a question my daughter asked me (she is apparently using google sheets). I have forwarded your answer and recommended she use a pivot table there as well.

As for me, I'm using excel for MAC version16. (386 subscription)

-Eric

Happy to help. But, of course the solution I gave is for an Excel solution.
Google Sheets questions should be posted here:

General Discussion & Other Applications

This forum is for discussion about any application & for questions about applications other than Excel or Access. This is also the place to ask about other spreadsheet programs (e.g. Google Sheets), book suggestions, or other websites.

www.mrexcel.com

Best Wishes!