SUM OF DAILY TRANSACTION VALUES INTO MONTHLY SUMMARY - MULTIPLE CRITERIA

[Craig Peter DG]

My problem is this:-

I have 24 Expense Category values, across 6 months of data recorded as daily transactions.

I need to summarise the daily transactional data into Monthly Totals for July 2023 through January 2024, for 19 Expense categories.

Not every Expense category has data recorded. These are blanks or zero dollar values. I am stuck on whether to use SUMPRODUCT, or SUMIFS, or INDEX MATCH, or which combination of these.

Here is a snapshot of my Source data:

OPERATING EXPENSES
Date	Total (Incl GST	VEHICLE	REPAIRS/MAINT	RENT (COMMERCIAL	PROMO/MARKETING	CONSUMABLES (STUDIO SUPPLIES)	HOME OFFICE SUPPLIES
Jul-23​	$ 192.90		$ 192.90
Jul-23​	$ 131.05		$ 131.05
Jul-23​	$ 4.85		$ 4.85
Jul-23​	$ 25.00
Jul-23​	$ 123.20
Jul-23​	$ 119.80
Jul-23​	$ 20.20
Jul-23​	$ 510.38
Jul-23​	$ 9.00					$ 9.00
Jul-23​	$ 17.95
Jul-23​	$ 84.67					$ 84.67
Jul-23​	$ 103.37					$ 103.37
Jul-23​	$ 150.00			$ 150.00
Jul-23​	$ 479.60
Jul-23​	$ 4,140.00		$ 4,140.00
Jul-23​	$ 11.00					$ 11.00
Jul-23​	$ 4.50					$ 4.50
Jul-23​	$ 58.90				$ 58.90
Jul-23​	$ 12.00					$ 12.00
Jul-23​	$ 150.00			$ 150.00
Jul-23​	$ 85.00			$ 85.00
Jul-23​	$ 85.00			$ 85.00
Jul-23​	$ 63.20					$ 63.20
Jul-23​	$ 16.40
Jul-23​	$ 470.00
Jul-23​	$ 25.00
Aug-23​	$ 750.00	$ 750.00
Aug-23​	$ 85.00			$ 85.00
Aug-23​	$ 15.00				$ 15.00
Aug-23​	$ 391.90
Aug-23​	$ 533.00
Aug-23​	$ 150.00			$ 150.00
Aug-23​	$ 40.70						$ 40.70
Aug-23​	$ 85.00			$ 85.00
Aug-23​	$ 83.40					$ 83.40
Aug-23​	$ 13.00					$ 13.00
Aug-23​	$ 555.04				$ 555.04
Aug-23​	$ 85.00			$ 85.00
Aug-23​	$ 31.73					$ 31.73
Aug-23​	$ 52.46
Aug-23​	$ 42.25					$ 42.25
Aug-23​	$ 5.99					$ 5.99
Aug-23​	$ 233.53
Aug-23​	$ 150.00			$ 150.00
Aug-23​	$ 462.00		$ 462.00
Aug-23​	$ 123.20
Aug-23​	$ 297.00		$ 297.00
Aug-23​	$ 13.15					$ 13.15
Aug-23​	$ 1,648.57
Aug-23​	$ 85.00			$ 85.00
Aug-23​	$ 7,085.10		$ 7,085.10
Aug-23​	$ 25.00
Aug-23​	$ 35.00					$ 35.00
Aug-23​	$ 23.89					$ 23.89
Aug-23​	$ 50.00				$ 50.00
Aug-23​	$ 176.90
Sep-23​	$ 4.80						$ 4.80
Sep-23​	$ 121.00
Sep-23​	$ 123.20
Sep-23​	$ 22.50						$ 22.50
Sep-23​	$ 38.50					$ 38.50
Sep-23​	$ 188.36					$ 188.36
Sep-23​	$ 69.00					$ 69.00
Sep-23​	$ 150.00			$ 150.00
Sep-23​	$ 8.50						$ 8.50
Sep-23​	$ 50.05
Sep-23​	$ 80.00			$ 80.00
Sep-23​	$ 35.00						$ 35.00
Sep-23​	$ 80.00			$ 80.00

And here is the destination table where I want to summarise the data into Monthly totals for each expense type:

Summary Table
Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8
Operating Expenses	Jul-23​	Aug-23​	Sep-23​	Oct-23​	Nov-23​	Dec-23​	Jan-24​
BANK/MERCHANT FEES
BOOKEEPING SERVICES
COMPLIANCE/REGULATORY
CONSUMABLES (STUDIO SUPPLIES)
DIGITAL SERVICES/WEB HOSTING
EDUCATION/TRAINING
HOME OFFICE SUPPLIES
INSURANCE (INCOME PROTECT)
LAUNDRY
PHONE/INTERNET
POS
POSTAGE/FREIGHT
PPE
PROMO/MARKETING
RENT (COMMERCIAL
REPAIRS/MAINT
SOFTWARE/HARDWARE
SUBS/MEMBERSHIPS
TOOLS & EQUIP
TRAVEL/ACCOM/MEALS
UTILITIES
VEHICLE

I have recieved some assistance but Im afraid Im not grasping the solutions offered. Would appreciate a simpler explanation.

Many thanks
Craig

sum each expense type column By month, based on daily transaction date

match expense types across columns $C$4 through $Z$4

to expense types in rows $A$190 through $A$214

 

[etaf]

I have recieved some assistance but Im afraid Im not grasping the solutions offered. Would appreciate a simpler explanation.

What solutions have you been offered - it maybe easier to see those and give an explanation , rather than just repeat the same solutions here
OR
ask for an explanation , to the person who suggested

Chances are you may get exactly the same solution here and still not understand

Are you still using excel 2013 version of excel

 

[Alex Blakenburg]

Without an XL2BB or something that gives exact rows and columns we are having to guess.
Also you refer to transactional data so I would expect the dates to include days but you are only giving mmm-yy, I have assumed that the transactional data include the day.

Results

20240212 Sumifs Index Match Craig Peter DG.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
187	Summary Table
188
189	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8
190	Operating Expenses	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	Feb-24	Mar-24	Apr-24	May-24	Jun-24	FY TOTAL
191	BANK/MERCHANT FEES	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
192	BOOKEEPING SERVICES	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
193	COMPLIANCE/REGULATORY	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
194	CONSUMABLES (STUDIO SUPPLIES)	$287.74	$544.27	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
195	DIGITAL SERVICES/WEB HOSTING	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
196	EDUCATION/TRAINING	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
197	HOME OFFICE SUPPLIES	$0.00	$111.50	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00
Data

Cell Formulas
Range	Formula
B191:M197	B191	=IFERROR(SUMIFS(INDEX($C$5:$Z$73,0,MATCH($A191,$C$4:$Z$4,0)),$A$5:$A$73,">="&B$190,$A$5:$A$73,"<="&EOMONTH(B$190,0)),0)

Source (only the first few lines)

20240212 Sumifs Index Match Craig Peter DG.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3	OPERATING EXPENSES
4	Date	Total (Incl GST	VEHICLE	REPAIRS/MAINT	RENT (COMMERCIAL	PROMO/MARKETING	CONSUMABLES (STUDIO SUPPLIES)	HOME OFFICE SUPPLIES
5	10/07/2023	$192.90		$192.90
6	1/07/2023	$131.05		$131.05
7	1/07/2023	$4.85		$4.85
8	1/07/2023	$25.00
9	1/07/2023	$123.20
10	1/07/2023	$119.80
11	1/07/2023	$20.20
12	1/07/2023	$510.38
13	1/07/2023	$9.00					$9.00
14	1/07/2023	$17.95
Data

 

[Georgiboy]

For reference: https://www.mrexcel.com/board/threads/simple-problem-tricky-solution.1253320/