Simple Problem, Tricky Solution

Craig Peter DG · Feb 6, 2024

Hi

My simple problem is that I need to Sum Values in a Table array within a given date range, with a unique identifying criteria. There are about 15 unique criterias and 365 days of separate dates for a years worth of data.

I need Totals by Month for each of the 15 unique identifiers......

This table shows where I want my results to go. These are Expense types.

Problem is, the values in the source data table are date by date by date, and I need those summarised for each Expense type into month by month, so, grouped, as it were. In addition, the expense categories you see in this table below, well in the source data, they run across the page in columns, not down the page in rows as you see here.

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column9

Column10

Column11

Column12

Column13

Column14

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

BANK/MERCHANT FEES

SUM ALL VALUES FOR JULY FOR THIS EXPENSE TYPE

$ -

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

$ -

Capital Purchases

$ -

COMPUTER/TABLET

$ -

MACHINE/EQUIP

$ -

So i have a Result table with a different layout to the source data table, I need to sum values that appear down columns in the source data, with different date formattting.

Help??????????? Perhaps an Index Match with an embedded Sumifs????

I dont know.

Im using Excel 2013.

Thankyou

Here is a snapshot of the Source data. See how the Expense categories in columns.

Monthly Expenditure - By Expense Category 2023-2024

OPERATING EXPENSES

CAPITAL PURCHASES

Date

Total (Incl GST

VEHICLE

REPAIRS/MAINT

RENT (COMMERCIAL

PROMO/MARKETING

CONSUMABLES (STUDIO SUPPLIES)

HOME OFFICE SUPPLIES

POS

DIGITAL SERVICES/WEB HOSTING

POSTAGE/FREIGHT

TOOLS & EQUIP

TRAVEL/ACCOM/MEALS

PHONE/INTERNET

UTILITIES

SUBS/MEMBERSHIPS

INSURANCE (INCOME PROTECT)

EDUCATION/TRAINING

BOOKEEPING SERVICES

SOFTWARE/HARDWARE

BANK/MERCHANT FEES

LAUNDRY

COMPLIANCE/REGULATORY

PPE

COMPUTER/TABLET

MACHINE/EQUIP

1/07/2023

$ 192.90

1/07/2023

$ 131.05

1/07/2023

$ 4.85

1/07/2023

$ 25.00

1/07/2023

$ 123.20

Georgiboy · Feb 10, 2024

You need to lock the column part at least, if you unlock the row part of the month check part then you need to unlock the row part of the sum range. This is because the sum range needs to be the same size as the month check range. Basically the sum columns and rows need to match the size of the check columns and rows.

Try it as below:
=SUMPRODUCT(--MONTH($A$5:$A174)=MONTH(B$182)*--($C$4:$Z$4=$A183),$C$5:$Z174)

Craig Peter DG · Feb 11, 2024

Georgiboy said:
You need to lock the column part at least, if you unlock the row part of the month check part then you need to unlock the row part of the sum range. This is because the sum range needs to be the same size as the month check range. Basically the sum columns and rows need to match the size of the check columns and rows.

Try it as below:
=SUMPRODUCT(--MONTH($A$5:$A174)=MONTH(B$182)*--($C$4:$Z$4=$A183),$C$5:$Z174)

Hi Georgieboy. Thankyou. I entered the new formula with some further locking of cells and columns. This time, all my N/A errors disappeared. For the first 5 expense categories down Column 1 i got Zero results as in 0, for all data between Jul 23 and Jan 24. All the rest of the remaining 19 Expense categories I got VALUE error. Here is a screenshot. For some reason the formula is not recognising ALL the expense types, and for the 5 that it is recognising, it is giving me a $0 sum value. Any further thoughts would be greatly appreciated.

Thankyou

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	0	0	0	0	0	0	0
BOOKEEPING SERVICES	0	0	0	0	0	0	0
COMPLIANCE/REGULATORY	0	0	0	0	0	0	0
CONSUMABLES (STUDIO SUPPLIES)	0	0	0	0	0	0	0
DIGITAL SERVICES/WEB HOSTING	0	0	0	0	0	0	0
EDUCATION/TRAINING	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
HOME OFFICE SUPPLIES	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
INSURANCE (INCOME PROTECT)	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
LAUNDRY	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
PHONE/INTERNET	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
POS	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
POSTAGE/FREIGHT	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
PPE	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
PROMO/MARKETING	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
RENT (COMMERCIAL	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
REPAIRS/MAINT	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
SOFTWARE/HARDWARE	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
SUBS/MEMBERSHIPS	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
TOOLS & EQUIP	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
TRAVEL/ACCOM/MEALS	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
UTILITIES	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
VEHICLE	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
Capital Purchases	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
COMPUTER/TABLET	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
MACHINE/EQUIP	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!

Georgiboy · Feb 12, 2024

See if the below helps, you do need to lock the rows due to the nature of the data.

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

1

OPERATING EXPENSES

CAPITAL PURCHASES

2

Date

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

Capital Purchases

COMPUTER/TABLET

MACHINE/EQUIP

3

01/07/2023

192.9

4

01/07/2023

131.05

100

400

999

5

01/07/2023

4.85

500

6

01/07/2023

25

7

01/07/2023

123.2

8

23/09/2023

192.9

9

23/09/2023

131.05

100

400

999

10

23/09/2023

4.85

300

500

11

23/09/2023

25

233

12

23/09/2023

123.2

13

14

15

16

17

18

19

20

21

22

23

24

25

26

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

27

BANK/MERCHANT FEES

28

BOOKEEPING SERVICES

0

29

COMPLIANCE/REGULATORY

30

CONSUMABLES (STUDIO SUPPLIES)

0

31

DIGITAL SERVICES/WEB HOSTING

0

32

EDUCATION/TRAINING

33

HOME OFFICE SUPPLIES

0

34

INSURANCE (INCOME PROTECT)

35

LAUNDRY

0

36

PHONE/INTERNET

0

37

POS

38

POSTAGE/FREIGHT

0

39

PPE

0

40

PROMO/MARKETING

41

RENT (COMMERCIAL

0

42

REPAIRS/MAINT

43

SOFTWARE/HARDWARE

0

44

SUBS/MEMBERSHIPS

45

TOOLS & EQUIP

0

46

TRAVEL/ACCOM/MEALS

0

47

UTILITIES

0

48

VEHICLE

0

49

Capital Purchases

50

COMPUTER/TABLET

0

51

MACHINE/EQUIP

0

Sheet2

Cell Formulas
Range	Formula
B27:M51	B27	=SUMPRODUCT(--(MONTH($A$3:$A$20)=MONTH(B$26))*--($B$2:$Z$2=$A27),$B$3:$Z$20)
N27:N51	N27	=SUM(B27:M27)

Craig Peter DG · Feb 12, 2024

Georgiboy said:
See if the below helps, you do need to lock the rows due to the nature of the data.

Book1
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
1 OPERATING EXPENSES CAPITAL PURCHASES
2 Date 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 Capital Purchases COMPUTER/TABLET MACHINE/EQUIP
3 01/07/2023 192.9 192.9
4 01/07/2023 131.05 131.05 100 400 999
5 01/07/2023 4.85 4.85 500
6 01/07/2023 25 25
7 01/07/2023 123.2 123.2
8 23/09/2023 192.9 192.9
9 23/09/2023 131.05 131.05 100 400 999
10 23/09/2023 4.85 4.85 300 500
11 23/09/2023 25 25 233
12 23/09/2023 123.2 123.2
13
14
15
16
17
18
19
20
21
22
23
24
25
26 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
27 BANK/MERCHANT FEES 477 0 477 0 0 0 0 0 0 0 0 0 954
28 BOOKEEPING SERVICES 0 0 0 0 0 0 0 0 0 0 0 0 0
29 COMPLIANCE/REGULATORY 328.8 0 328.8 0 0 0 0 0 0 0 0 0 657.6
30 CONSUMABLES (STUDIO SUPPLIES) 0 0 0 0 0 0 0 0 0 0 0 0 0
31 DIGITAL SERVICES/WEB HOSTING 0 0 0 0 0 0 0 0 0 0 0 0 0
32 EDUCATION/TRAINING 0 0 300 0 0 0 0 0 0 0 0 0 300
33 HOME OFFICE SUPPLIES 0 0 0 0 0 0 0 0 0 0 0 0 0
34 INSURANCE (INCOME PROTECT) 25 0 25 0 0 0 0 0 0 0 0 0 50
35 LAUNDRY 0 0 0 0 0 0 0 0 0 0 0 0 0
36 PHONE/INTERNET 0 0 0 0 0 0 0 0 0 0 0 0 0
37 POS 100 0 100 0 0 0 0 0 0 0 0 0 200
38 POSTAGE/FREIGHT 0 0 0 0 0 0 0 0 0 0 0 0 0
39 PPE 0 0 0 0 0 0 0 0 0 0 0 0 0
40 PROMO/MARKETING 400 0 400 0 0 0 0 0 0 0 0 0 800
41 RENT (COMMERCIAL 0 0 0 0 0 0 0 0 0 0 0 0 0
42 REPAIRS/MAINT 0 0 233 0 0 0 0 0 0 0 0 0 233
43 SOFTWARE/HARDWARE 0 0 0 0 0 0 0 0 0 0 0 0 0
44 SUBS/MEMBERSHIPS 623.2 0 623.2 0 0 0 0 0 0 0 0 0 1246.4
45 TOOLS & EQUIP 0 0 0 0 0 0 0 0 0 0 0 0 0
46 TRAVEL/ACCOM/MEALS 0 0 0 0 0 0 0 0 0 0 0 0 0
47 UTILITIES 0 0 0 0 0 0 0 0 0 0 0 0 0
48 VEHICLE 0 0 0 0 0 0 0 0 0 0 0 0 0
49 Capital Purchases 999 0 999 0 0 0 0 0 0 0 0 0 1998
50 COMPUTER/TABLET 0 0 0 0 0 0 0 0 0 0 0 0 0
51 MACHINE/EQUIP 0 0 0 0 0 0 0 0 0 0 0 0 0
Sheet2
Cell Formulas
Range Formula
B27:M51 B27 =SUMPRODUCT(--(MONTH($A$3:$A$20)=MONTH(B$26))*--($B$2:$Z$2=$A27),$B$3:$Z$20)
N27:N51 N27 =SUM(B27:M27)

Thankyou Georgieboy. Your solution was amazing and worked across all Rows and Columns in my destination table. I have trouble grasping the concept of SumProduct still, it seems to work like Index Match in some regards am i right in saying that, cause the entire coordinates where the data you need to add. Anyway MANY THANKS, I will study this function in detail now and try to use it more often.

2023-2024 EXPENSE SUMMARY TABLE
Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column14
Operating Expenses	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24	FY TOTAL
BANK/MERCHANT FEES	$ -	$ -	$ 0.37	$ 0.95	$ 1.40	$ -	$ -	$ 2.72
BOOKEEPING SERVICES	$ 123.20	$ 123.20	$ 123.20	$ 246.40	$ 250.00	$ -	$ -	$ 866.00
COMPLIANCE/REGULATORY	$ -	$ -	$ -	$ 115.00	$ 2.50	$ 45.10	$ -	$ 162.60
CONSUMABLES (STUDIO SUPPLIES)	$ 287.74	$ 248.41	$ 418.49	$ 8.74	$ 390.23	$ 46.30	$ 157.05	$ 1,556.96
DIGITAL SERVICES/WEB HOSTING	$ 119.80	$ -	$ 121.00	$ -	$ 120.20	$ -	$ 112.80	$ 473.80
EDUCATION/TRAINING	$ -	$ -	$ -	$ 75.00	$ -	$ -	$ -	$ 75.00
HOME OFFICE SUPPLIES	$ -	$ 40.70	$ 70.80	$ 75.80	$ 155.08	$ -	$ -	$ 342.38
INSURANCE (INCOME PROTECT)	$ -	$ 1,648.57	$ -	$ -	$ -	$ -	$ -	$ 1,648.57
LAUNDRY	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
PHONE/INTERNET	$ 36.60	$ -	$ 15.40	$ 20.20	$ 16.40	$ -	$ -	$ 88.60
POS	$ 50.00	$ 25.00	$ 25.00	$ 30.00	$ 30.00	$ 30.00	$ -	$ 190.00
POSTAGE/FREIGHT	$ -	$ -	$ -	$ -	$ 73.85	$ -	$ -	$ 73.85
PPE	$ -	$ -	$ 312.39	$ -	$ 42.00	$ -	$ 39.00	$ 393.39
PROMO/MARKETING	$ 58.90	$ 620.04	$ -	$ 111.20	$ 206.53	$ 338.87	$ -	$ 1,335.54
RENT (COMMERCIAL	$ 470.00	$ 640.00	$ 540.00	$ 550.00	$ 555.00	$ 320.00	$ 150.00	$ 3,225.00
REPAIRS/MAINT	$ 4,468.80	$ 7,844.10	$ -	$ 4,185.18	$ 926.65	$ 495.00	$ -	$17,919.73
SOFTWARE/HARDWARE	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
SUBS/MEMBERSHIPS	$ -	$ -	$ -	$ -	$ -	$ 1,026.00	$ -	$ 1,026.00
TOOLS & EQUIP	$ 479.60	$ 52.46	$ -	$ 438.10	$ 482.98	$ -	$ -	$ 1,453.14
TRAVEL/ACCOM/MEALS	$ 17.95	$ 176.90	$ 80.75	$ 24.00	$ -	$ 11.40	$ 780.30	$ 1,091.30
UTILITIES	$ 510.38	$ 1,158.43	$ 386.23	$ -	$ 233.01	$ 812.25	$ -	$ 3,100.30
VEHICLE	$ -	$ 750.00	$ -	$ -	$ 770.39	$ 1,428.81	$ -	$ 2,949.20
Capital Purchases	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
COMPUTER/TABLET	$ -	$ -	$ -	$ 456.95	$ -	$ -	$ -	$ 456.95
MACHINE/EQUIP	$ 470.00	$ -	$ -	$ -	$ -	$ -	$ -	$ 470.00
Total								$38,901.03

Georgiboy · Feb 12, 2024

No problem, I use Exceljet for explanations, see below link:
SUMPRODUCT with IF

Craig Peter DG · Feb 12, 2024

Georgiboy said:
No problem, I use Exceljet for explanations, see below link:
SUMPRODUCT with IF

Do you prefer Sum Product over Index Match SumIfs? Is it something that you are just used to doing for Summing with Multiple Criteria? Have you always used Sum Product?

Georgiboy · Feb 12, 2024

I like Sumproduct, I have used it for quite a number of years. I have always used it, but less so, now we have the newer functions available in Excel 365.
I started using Excel with the 97-2003 version so it has always been useful.

Simple Problem, Tricky Solution

Craig Peter DG

New Member

Georgiboy

Well-known Member

Excel Facts

Craig Peter DG

New Member

Georgiboy

Well-known Member

Craig Peter DG

New Member

Georgiboy

Well-known Member

Craig Peter DG

New Member

Georgiboy

Well-known Member

Similar threads

Forum statistics

Share this page

Simple Problem, Tricky Solution

New Member

Well-known Member

Excel Facts

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock