SUM and Filter Formula

[ChetanPuri]

Dear Excel Experts,

Can you please advise what am I doing wrong here. I am trying to create a summaryy by Work ID, then codes column Aand then matching the headerss. Any hep will be appreciated.
Many thanks,

V17 BAS Review 05 December 2024.xlsm
	A	B	C	D	E
1		Period	JUL24	AUG24
2		Costing Work ID	10	Monthly
3	CODES		Ex GST	GST Amount	GST Inclusive
4	GST Codes - Purchase	BAS Codes	$ 0	$ 0	$ 0
5	A1	G11	#VALUE!	$0.00	$0.00
6	A2	G14	$0.00	$0.00	$0.00
7	A4	G15	$0.00	$0.00	$0.00
8	A5	G10	$0.00	$0.00	$0.00
9	C1	G1	$0.00	$0.00	$0.00
10	S1	G2	$0.00	$0.00	$0.00
11	S2		$0.00	$0.00	$0.00
12	Total		#VALUE!	$0.00	$0.00
BAS SUMMARY _MONTHLY

Cell Formulas
Range	Formula
C5	C5	=SUM(FILTER('GST Reconciliation'!$D$4:$F$33,('GST Reconciliation'!$D$2='BAS SUMMARY _MONTHLY'!$C$2)*('GST Reconciliation'!$A$5:$A$33,'BAS SUMMARY _MONTHLY'!A5)))
C12:E12	C12	=SUM(C5:C11)

Cells with Data Validation
Cell	Allow	Criteria
C1:D1	List	='All Transaction'!$F:$F

V17 BAS Review 05 December 2024.xlsm
	A	B	C	D	E	F
1			Period	JUL24	SEP24
2			Costing Work ID	10	Monthly
3	CODES		Transaction Type	Ex GST	GST Amount	GST Inclusive
4	GST Codes - Purchase	BAS Codes		$ 0	$ 0	$ 0
5	A0	Not Required For BAS	AP INV	$ 2,060,521	$ 10,877	$ 2,071,398
6	A0	Not Required For BAS	DR DBT	$ 0	$ 0	$ 0
7	A0	Not Required For BAS	AP CRD	($ (1,623,255))	$ 0	($ (1,623,255))
8	A0	Not Required For BAS	AP PPI	$ 29,432	$ 0	$ 29,432
9	A0	Not Required For BAS	AP PPC	($ (11,550))	$ 0	($ (11,550))
10	A1	G11	AP INV	$ 25,029,828	$ 2,502,983	$ 27,532,811
11	A1	G11	AP CRD	($ (173,748))	($ (17,375))	($ (191,123))
12	A1	G11	DR DBT	$ 0	$ 0	$ 0
13	A1	G11	AR ADJ	$ 0	$ 0	$ 0
14	A1	G11	AP PPC	$ 1,268	$ 127	$ 1,394
15	A1	G11	AP PPI	$ 552	$ 55	$ 607
16	A2	G14	AP PPI	$ 234	$ 0	$ 234
17	A2	G14	AP INV	$ 248,035	$ 0	$ 248,035
18	A2	G14	AP CRD	($ (50,914))	$ 0	($ (50,914))
19	A4	G15	AP INV	$ 4,280	$ 0	$ 4,280
20	A4	G15	AP CRD	($ (7))	$ 0	($ (7))
21	A5	G10	AP INV	$ 0	$ 0	$ 0
22	C1	G1	G JNL	$ 0	$ 0	$ 0
23	C1	G1	AP PPI	$ 0	$ 0	$ 0
24	S0	G3	AR INV	$ 0	$ 0	$ 0
25	S0	G3	AR ADJ	$ 0	$ 0	$ 0
26	S0	G3	G JNL	$ 0	$ 0	$ 0
27	S0	G3	AR CSH	$ 0	$ 0	$ 0
28	S1	G2	AR INV	$ 0	$ 0	$ 0
29	S1	G2	AR ADJ	$ 0	$ 0	$ 0
30	S1	G2	AR CRD	($ (0))	$ 0	($ (0))
31	S1	G2	AR CSH	$ 0	$ 0	$ 0
32	S1	G2	G JNL	$ 0	$ 0	$ 0
33	S2		AR INV	$ 0	$ 0	$ 0
34	Total			$ 25,514,677	$ 2,496,667	$ 28,011,344
GST Reconciliation

Cell Formulas
Range	Formula
D34	D34	=SUM($D$4:$D$33)
E34	E34	=SUM($E$4:$E$33)
F34	F34	=SUM($F$4:$F$33)

Cells with Data Validation
Cell	Allow	Criteria
D1:E1	List	='All Transaction'!$F:$F

 

[ChetanPuri]

 

[ChetanPuri]

Still get this error

V17 BAS Review 05 December 2024.xlsm
	A	B	C	D	E	F
1		Period	JUL24	JUL24
2		Costing Work ID	10	Monthly
3	CODES		Ex GST	GST Amount	GST Inclusive
4	GST Codes - Purchase	BAS Codes	$ 0	$ 0	$ 0
5	A1	G11	#VALUE!	$737,322.52	$8,110,547.26
6	A2	G14	$35,863.48	$0.00	$35,863.48
7	A4	G15	$4,096.60	$0.00	$4,096.60
8	A5	G10	$0.00	$0.00	$0.00
9	C1	G1	$0.00	$0.00	$0.00
10	S1	G2	$0.00	$0.00	$0.00
11	S2		$0.00	$0.00	$0.00
12	Total		#VALUE!	$737,322.52	$8,150,507.34
BAS SUMMARY _MONTHLY

Cell Formulas
Range	Formula
C5	C5	=SUM(CHOOSECOLS(FILTER('GST Reconciliation'!$A$2:$U$34,'GST Reconciliation'!$A$2:$A$34,'BAS SUMMARY _MONTHLY'!A5),SEQUENCE(3,,XMATCH('BAS SUMMARY _MONTHLY'!$C$2,'GST Reconciliation'!$A$2:$U$2))))
D5:E5,C6:E11	D5	=SUMIFS('GST Reconciliation'!E:E,'GST Reconciliation'!$A:$A,'BAS SUMMARY _MONTHLY'!$A:$A)
C12:E12	C12	=SUM(C5:C11)

Cells with Data Validation
Cell	Allow	Criteria
C1:D1	List	='All Transaction'!$F:$F

 

[Cubist]

Replace with appropriate range. Formula in D38.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1			Period	24-Jul	24-Sep						Period	24-Jul	24-Sep					Period	24-Jul	24-Sep
2			Costing Work ID	10	Monthly						Costing Work ID	21	Quarterly					Costing Work ID	30	Quaterly
3	CODES		Transaction Type	Ex GST	GST Amount	GST Inclusive			CODES		Transaction Type	Ex GST	GST Amount	GST Inclusive		CODES		Transaction Type	Ex GST	GST Amount	GST Inclusive
4	GST Codes - Purchase	BAS Codes		0	0	0			GST Codes - Purchase	BAS Codes		0	0	0		GST Codes - Purchase	BAS Codes		0	0	0
5	A0	Not Required For BAS	AP INV	2060520.98	10877.06	2071398.04			A0	Not Required For BAS	AP INV	7261.09	0	7261.09		A0	Not Required For BAS	AP INV	22069	0	22069
6	A0	Not Required For BAS	DR DBT	0	0	0			A0	Not Required For BAS	DR DBT	0	0	0		A0	Not Required For BAS	DR DBT	0	0	0
7	A0	Not Required For BAS	AP CRD	-1623255.14	0	-1623255.14			A0	Not Required For BAS	AP CRD	0	0	0		A0	Not Required For BAS	AP CRD	-1950	0	-1950
8	A0	Not Required For BAS	AP PPI	29432.26	0	29432.26			A0	Not Required For BAS	AP PPI	0	0	0		A0	Not Required For BAS	AP PPI	0	0	0
9	A0	Not Required For BAS	AP PPC	-11550	0	-11550			A0	Not Required For BAS	AP PPC	0	0	0		A0	Not Required For BAS	AP PPC	0	0	0
10	A1	G11	AP INV	25029828.23	2502983.11	27532811.34			A1	G11	AP INV	2523349.49	252335.04	2775684.53		A1	G11	AP INV	6015937.52	601593.92	6617531.44
11	A1	G11	AP CRD	-173747.75	-17374.77	-191122.52			A1	G11	AP CRD	-1389.41	-138.92	-1528.33		A1	G11	AP CRD	-24656.43	-2465.67	-27122.1
12	A1	G11	DR DBT	0	0	0			A1	G11	DR DBT	0	0	0		A1	G11	DR DBT	0	0	0
13	A1	G11	AR ADJ	0	0	0			A1	G11	AR ADJ	0	0	0		A1	G11	AR ADJ	0	0	0
14	A1	G11	AP PPC	1267.7	126.77	1394.47			A1	G11	AP PPC	0	0	0		A1	G11	AP PPC	0	0	0
15	A1	G11	AP PPI	552	55.2	607.2			A1	G11	AP PPI	0	0	0		A1	G11	AP PPI	0	0	0
16	A2	G14	AP PPI	234.05	0	234.05			A2	G14	AP PPI	0	0	0		A2	G14	AP PPI	54783	0	54783
17	A2	G14	AP INV	248034.81	0	248034.81			A2	G14	AP INV	17602.75	0	17602.75		A2	G14	AP INV	20642.21	0	20642.21
18	A2	G14	AP CRD	-50913.54	0	-50913.54			A2	G14	AP CRD	-719	0	-719		A2	G14	AP CRD	-38143.68	0	-38143.68
19	A4	G15	AP INV	4279.96	0	4279.96			A4	G15	AP INV	159.5	0	159.5		A4	G15	AP INV	7573.99	0	7573.99
20	A4	G15	AP CRD	-7	0	-7			A4	G15	AP CRD	0	0	0		A4	G15	AP CRD	0	0	0
21	A5	G10	AP INV	0	0	0			A5	G10	AP INV	74000	7400	81400		A5	G10	AP INV	0	0	0
22	C1	G1	G JNL	0	0	0			C1	G1	G JNL	0	0	0		C1	G1	G JNL	0	0	0
23	C1	G1	AP PPI	0	0	0			C1	G1	AP PPI	0	0	0		C1	G1	AP PPI	0	0	0
24	S0	G3	AR INV	0	0	0			S0	G3	AR INV	-5.24E-12	0	0		S0	G3	AR INV	0	0	0
25	S0	G3	AR ADJ	0	0	0			S0	G3	AR ADJ	0	0	0		S0	G3	AR ADJ	0	0	0
26	S0	G3	G JNL	0	0	0			S0	G3	G JNL	0	0	0		S0	G3	G JNL	0	0	0
27	S0	G3	AR CSH	0	0	0			S0	G3	AR CSH	0	0	0		S0	G3	AR CSH	0	0	0
28	S1	G2	AR INV	1.69E-09	0	1.69E-09			S1	G2	AR INV	1.06E-09	0	0		S1	G2	AR INV	5.97E-13	0	0
29	S1	G2	AR ADJ	4.66E-10	0	4.66E-10			S1	G2	AR ADJ	-3.64E-12	0	0		S1	G2	AR ADJ	0	0	0
30	S1	G2	AR CRD	-5.33E-15	0	-5.33E-15			S1	G2	AR CRD	-7.39E-13	0	0		S1	G2	AR CRD	0	0	0
31	S1	G2	AR CSH	0	0	0			S1	G2	AR CSH	0	0	0		S1	G2	AR CSH	0	0	0
32	S1	G2	G JNL	0	0	0			S1	G2	G JNL	0	0	0		S1	G2	G JNL	0	0	0
33	S2		AR INV	0	0	0			S2		AR INV	0	0	0		S2		AR INV	0	0	0
34	Total			25514676.56	2496667.37	28011343.93			Total			2620264.42	259596.12	2879860.54		Total			6056255.61	599128.25	6655383.86
35
36
37
38	10	A0		455148.1	10877.06	466025.16
Sheet4

Cell Formulas
Range	Formula
D34	D34	=SUM($D$4:$D$33)
E34	E34	=SUM($E$4:$E$33)
F34	F34	=SUM($F$4:$F$33)
L34	L34	=SUM($L$4:$L$33)
M34	M34	=SUM($M$4:$M$33)
N34	N34	=SUM($N$4:$N$33)
S34	S34	=SUM($S$4:$S$33)
T34	T34	=SUM($T$4:$T$33)
U34	U34	=SUM($U$4:$U$33)
D38:F38	D38	=BYCOL(CHOOSECOLS(FILTER($A$2:$U$34,$A$2:$A$34=B38),SEQUENCE(3,,XMATCH(A38,$A$2:$U$2))),LAMBDA(x,SUM(x)))
Dynamic array formulas.

 

[ChetanPuri]

it does work at the bottom of the sheet is there a way i can use it on my summary sheet

 

[Cubist]

it does work at the bottom of the sheet is there a way i can use it on my summary sheet

Cut the result and paste it into the desired sheet.

 

[ChetanPuri]

for LAMDA do I need to define it in name manger?

 

[Cubist]

Not in this case.

 

[ChetanPuri]

Not in this case.

okay great, I will give it a go many thanks, regards, Chetan

 

[ChetanPuri]

okay great, I will give it a go many thanks, regards, Chetan

I am sorry, I tried and get this

V17 BAS Review 05 December 2024.xlsm
	A	B	C	D	E
1		Period	SEP24	SEP24
2		Costing Work ID	10	Monthly
3	CODES		Ex GST	GST Amount	GST Inclusive
4	GST Codes - Purchase	BAS Codes	$ 0	$ 0	$ 0
5	A1	G11	#SPILL!	#CALC!	#SPILL!
6	A2	G14	#SPILL!	#CALC!	#SPILL!
7	A4	G15	#SPILL!	#CALC!	#SPILL!
8	A5	G10	#SPILL!	#CALC!	#SPILL!
9	C1	G1	#SPILL!	#CALC!	#SPILL!
10	S1	G2	#SPILL!	#CALC!	#SPILL!
11	S2		#CALC!	10.00	#CALC!
12	Total		#SPILL!	#CALC!	#SPILL!
BAS SUMMARY _MONTHLY

Cell Formulas
Range	Formula
C5:C11	C5	=BYCOL(CHOOSECOLS(FILTER('GST Reconciliation'!$A$2:$U$23,'GST Reconciliation'!$A$2:$A$23=A5),SEQUENCE(3,,XMATCH('BAS SUMMARY _MONTHLY'!$C$2,'GST Reconciliation'!$A$2:$U$2))),LAMBDA(x,SUM(x)))
D5:E11	D5	=@BYCOL(CHOOSECOLS(FILTER('GST Reconciliation'!$A$2:$U$23,'GST Reconciliation'!$A$2:$A$23=B5),SEQUENCE(3,,XMATCH('BAS SUMMARY _MONTHLY'!$C$2,'GST Reconciliation'!$A$2:$U$2))),LAMBDA(x,SUM(x)))
C12:E12	C12	=SUM(C5:C11)

Cells with Data Validation
Cell	Allow	Criteria
C1:D1	List	='All Transaction'!$F:$F

 

[Cubist]

Delete formulas in column D and E.