SUM and Filter Formula

ChetanPuri · Dec 6, 2024

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

5

A1

G11

#VALUE!

$0.00

6

A2

G14

$0.00

7

A4

G15

$0.00

8

A5

G10

$0.00

9

C1

G1

$0.00

10

S1

G2

$0.00

11

S2

$0.00

12

Total

#VALUE!

$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

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

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

13

A1

G11

AR ADJ

$ 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

22

C1

G1

G JNL

$ 0

23

C1

G1

AP PPI

$ 0

24

S0

G3

AR INV

$ 0

25

S0

G3

AR ADJ

$ 0

26

S0

G3

G JNL

$ 0

27

S0

G3

AR CSH

$ 0

28

S1

G2

AR INV

$ 0

29

S1

G2

AR ADJ

$ 0

30

S1

G2

AR CRD

($ (0))

$ 0

($ (0))

31

S1

G2

AR CSH

$ 0

32

S1

G2

G JNL

$ 0

33

S2

AR INV

$ 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

Cubist · Dec 6, 2024

What's the reason for the red part?

Rich (BB code):

=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)))

ChetanPuri · Dec 6, 2024

Cubist said:
What's the reason for the red part?

Rich (BB code):

=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)))

Not sure as I am trying to match work Id which is in D2 of source worksheet and C2 in the result worksheet not sure what could be the case

Cubist · Dec 6, 2024

Are there more than 1 work ID? I only see one in the mini-sheet.

ChetanPuri · Dec 6, 2024

Cubist said:
Are there more than 1 work ID? I only see one in the mini-sheet.

There are but I am just referencing to one only that is in D2

Cubist · Dec 6, 2024

Can you repost the sheet with multiple ID's?

ChetanPuri · Dec 6, 2024

Cubist said:
Can you repost the sheet with multiple ID's?

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

5

A1

G11

$54,687,380.98

#CALC!

6

A2

G14

$394,710.64

$0.00

7

A4

G15

$8,545.92

$0.00

8

A5

G10

$0.00

9

C1

G1

$0.00

10

S1

G2

$0.00

11

S2

$0.00

12

Total

$55,090,637.54

#CALC!

BAS SUMMARY _MONTHLY

Cell Formulas
Range	Formula
C5:E5	D5	=SUM(FILTER('GST Reconciliation'!$D$5:$F$33,('GST Reconciliation'!$A$5:$A$33='BAS SUMMARY _MONTHLY'!B5)('GST Reconciliation'!$D$2'BAS SUMMARY _MONTHLY'!$C$2)*('GST Reconciliation'!E3='BAS SUMMARY _MONTHLY'!D3)))
C6:C11	C6	=SUM(FILTER('GST Reconciliation'!$D$5:$F$33,('GST Reconciliation'!$A$5:$A$33='BAS SUMMARY _MONTHLY'!A6)))
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

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Period

JUL24

SEP24

Period

JUL24

SEP24

Period

JUL24

SEP24

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

GST Codes - Purchase

BAS Codes

$ 0

GST Codes - Purchase

BAS Codes

$ 0

5

A0

Not Required For BAS

AP INV

$ 2,060,521

$ 10,877

$ 2,071,398

A0

Not Required For BAS

AP INV

$ 7,261

$ 0

$ 7,261

A0

Not Required For BAS

AP INV

$ 22,069

$ 0

$ 22,069

6

A0

Not Required For BAS

DR DBT

$ 0

A0

Not Required For BAS

DR DBT

$ 0

A0

Not Required For BAS

DR DBT

$ 0

7

A0

Not Required For BAS

AP CRD

($ (1,623,255))

$ 0

($ (1,623,255))

A0

Not Required For BAS

AP CRD

$ 0

A0

Not Required For BAS

AP CRD

($ (1,950))

$ 0

($ (1,950))

8

A0

Not Required For BAS

AP PPI

$ 29,432

$ 0

$ 29,432

A0

Not Required For BAS

AP PPI

$ 0

A0

Not Required For BAS

AP PPI

$ 0

9

A0

Not Required For BAS

AP PPC

($ (11,550))

$ 0

($ (11,550))

A0

Not Required For BAS

AP PPC

$ 0

A0

Not Required For BAS

AP PPC

$ 0

10

A1

G11

AP INV

$ 25,029,828

$ 2,502,983

$ 27,532,811

A1

G11

AP INV

$ 2,523,349

$ 252,335

$ 2,775,685

A1

G11

AP INV

$ 6,015,938

$ 601,594

$ 6,617,531

11

A1

G11

AP CRD

($ (173,748))

($ (17,375))

($ (191,123))

A1

G11

AP CRD

($ (1,389))

($ (139))

($ (1,528))

A1

G11

AP CRD

($ (24,656))

($ (2,466))

($ (27,122))

12

A1

G11

DR DBT

$ 0

A1

G11

DR DBT

$ 0

A1

G11

DR DBT

$ 0

13

A1

G11

AR ADJ

$ 0

A1

G11

AR ADJ

$ 0

A1

G11

AR ADJ

$ 0

14

A1

G11

AP PPC

$ 1,268

$ 127

$ 1,394

A1

G11

AP PPC

$ 0

A1

G11

AP PPC

$ 0

15

A1

G11

AP PPI

$ 552

$ 55

$ 607

A1

G11

AP PPI

$ 0

A1

G11

AP PPI

$ 0

16

A2

G14

AP PPI

$ 234

$ 0

$ 234

A2

G14

AP PPI

$ 0

A2

G14

AP PPI

$ 54,783

$ 0

$ 54,783

17

A2

G14

AP INV

$ 248,035

$ 0

$ 248,035

A2

G14

AP INV

$ 17,603

$ 0

$ 17,603

A2

G14

AP INV

$ 20,642

$ 0

$ 20,642

18

A2

G14

AP CRD

($ (50,914))

$ 0

($ (50,914))

A2

G14

AP CRD

($ (719))

$ 0

($ (719))

A2

G14

AP CRD

($ (38,144))

$ 0

($ (38,144))

19

A4

G15

AP INV

$ 4,280

$ 0

$ 4,280

A4

G15

AP INV

$ 160

$ 0

$ 160

A4

G15

AP INV

$ 7,574

$ 0

$ 7,574

20

A4

G15

AP CRD

($ (7))

$ 0

($ (7))

A4

G15

AP CRD

$ 0

A4

G15

AP CRD

$ 0

21

A5

G10

AP INV

$ 0

A5

G10

AP INV

$ 74,000

$ 7,400

$ 81,400

A5

G10

AP INV

$ 0

22

C1

G1

G JNL

$ 0

C1

G1

G JNL

$ 0

C1

G1

G JNL

$ 0

23

C1

G1

AP PPI

$ 0

C1

G1

AP PPI

$ 0

C1

G1

AP PPI

$ 0

24

S0

G3

AR INV

$ 0

S0

G3

AR INV

($ (0))

$ 0

S0

G3

AR INV

$ 0

25

S0

G3

AR ADJ

$ 0

S0

G3

AR ADJ

$ 0

S0

G3

AR ADJ

$ 0

26

S0

G3

G JNL

$ 0

S0

G3

G JNL

$ 0

S0

G3

G JNL

$ 0

27

S0

G3

AR CSH

$ 0

S0

G3

AR CSH

$ 0

S0

G3

AR CSH

$ 0

28

S1

G2

AR INV

$ 0

S1

G2

AR INV

$ 0

S1

G2

AR INV

$ 0

29

S1

G2

AR ADJ

$ 0

S1

G2

AR ADJ

($ (0))

$ 0

S1

G2

AR ADJ

$ 0

30

S1

G2

AR CRD

($ (0))

$ 0

($ (0))

S1

G2

AR CRD

($ (0))

$ 0

S1

G2

AR CRD

$ 0

31

S1

G2

AR CSH

$ 0

S1

G2

AR CSH

$ 0

S1

G2

AR CSH

$ 0

32

S1

G2

G JNL

$ 0

S1

G2

G JNL

$ 0

S1

G2

G JNL

$ 0

33

S2

AR INV

$ 0

S2

AR INV

$ 0

S2

AR INV

$ 0

34

Total

Total

Total

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)
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)

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

ChetanPuri · Dec 6, 2024

ChetanPuri said:
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 $54,687,380.98 #CALC! #CALC!
6 A2 G14 $394,710.64 $0.00 $0.00
7 A4 G15 $8,545.92 $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 $55,090,637.54 #CALC! #CALC!
BAS SUMMARY _MONTHLY
Cell Formulas
Range Formula
C5:E5 D5 =SUM(FILTER('GST Reconciliation'!$D$5:$F$33,('GST Reconciliation'!$A$5:$A$33='BAS SUMMARY _MONTHLY'!B5)*('GST Reconciliation'!$D$2*'BAS SUMMARY _MONTHLY'!$C$2)*('GST Reconciliation'!E3='BAS SUMMARY _MONTHLY'!D3)))
C6:C11 C6 =SUM(FILTER('GST Reconciliation'!$D$5:$F$33,('GST Reconciliation'!$A$5:$A$33='BAS SUMMARY _MONTHLY'!A6)))
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 H I J K L M N O P Q R S T U
1 Period JUL24 SEP24 Period JUL24 SEP24 Period JUL24 SEP24
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 $ 2,060,521 $ 10,877 $ 2,071,398 A0 Not Required For BAS AP INV $ 7,261 $ 0 $ 7,261 A0 Not Required For BAS AP INV $ 22,069 $ 0 $ 22,069
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 ($ (1,623,255)) $ 0 ($ (1,623,255)) A0 Not Required For BAS AP CRD $ 0 $ 0 $ 0 A0 Not Required For BAS AP CRD ($ (1,950)) $ 0 ($ (1,950))
8 A0 Not Required For BAS AP PPI $ 29,432 $ 0 $ 29,432 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 ($ (11,550)) $ 0 ($ (11,550)) 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 $ 25,029,828 $ 2,502,983 $ 27,532,811 A1 G11 AP INV $ 2,523,349 $ 252,335 $ 2,775,685 A1 G11 AP INV $ 6,015,938 $ 601,594 $ 6,617,531
11 A1 G11 AP CRD ($ (173,748)) ($ (17,375)) ($ (191,123)) A1 G11 AP CRD ($ (1,389)) ($ (139)) ($ (1,528)) A1 G11 AP CRD ($ (24,656)) ($ (2,466)) ($ (27,122))
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 $ 1,268 $ 127 $ 1,394 A1 G11 AP PPC $ 0 $ 0 $ 0 A1 G11 AP PPC $ 0 $ 0 $ 0
15 A1 G11 AP PPI $ 552 $ 55 $ 607 A1 G11 AP PPI $ 0 $ 0 $ 0 A1 G11 AP PPI $ 0 $ 0 $ 0
16 A2 G14 AP PPI $ 234 $ 0 $ 234 A2 G14 AP PPI $ 0 $ 0 $ 0 A2 G14 AP PPI $ 54,783 $ 0 $ 54,783
17 A2 G14 AP INV $ 248,035 $ 0 $ 248,035 A2 G14 AP INV $ 17,603 $ 0 $ 17,603 A2 G14 AP INV $ 20,642 $ 0 $ 20,642
18 A2 G14 AP CRD ($ (50,914)) $ 0 ($ (50,914)) A2 G14 AP CRD ($ (719)) $ 0 ($ (719)) A2 G14 AP CRD ($ (38,144)) $ 0 ($ (38,144))
19 A4 G15 AP INV $ 4,280 $ 0 $ 4,280 A4 G15 AP INV $ 160 $ 0 $ 160 A4 G15 AP INV $ 7,574 $ 0 $ 7,574
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 $ 74,000 $ 7,400 $ 81,400 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 ($ (0)) $ 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 $ 0 $ 0 $ 0 S1 G2 AR INV $ 0 $ 0 $ 0 S1 G2 AR INV $ 0 $ 0 $ 0
29 S1 G2 AR ADJ $ 0 $ 0 $ 0 S1 G2 AR ADJ ($ (0)) $ 0 $ 0 S1 G2 AR ADJ $ 0 $ 0 $ 0
30 S1 G2 AR CRD ($ (0)) $ 0 ($ (0)) S1 G2 AR CRD ($ (0)) $ 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 $ 25,514,677 $ 2,496,667 $ 28,011,344 Total $ 2,620,264 $ 259,596 $ 2,879,861 Total $ 6,056,256 $ 599,128 $ 6,655,384
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)
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)
Cells with Data Validation
Cell Allow Criteria
D1:E1 List ='All Transaction'!$F:$F
L1:M1 List ='All Transaction'!$F:$F
S1:T1 List ='All Transaction'!$F:$F

ChetanPuri · Dec 6, 2024

I even tried sumifs, which doesn't work either

Cubist · Dec 6, 2024

Try:

Excel Formula:

=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))))

SUM and Filter Formula

ChetanPuri

Board Regular

Excel Facts

Cubist

Well-known Member

ChetanPuri

Board Regular

Cubist

Well-known Member

ChetanPuri

Board Regular

Cubist

Well-known Member

ChetanPuri

Board Regular

ChetanPuri

Board Regular

ChetanPuri

Board Regular

Cubist

Well-known Member

Similar threads

Forum statistics

Share this page

SUM and Filter Formula

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

Board Regular

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