SUMIFS or any other formula that can help

C

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
97
Office Version
  1. 365
Platform
  1. Windows
Dear Expert Users,

I need some help to build a check at the bottom of the worksheet say on row 30 of my GST Reconciliation worksheet. As you can see I have data in column D which equal2.6 m. So on row 30 I should be able to calculate and check it back to the source data, but unfortunately it doesn't work. In formula, I have included what I am trying to include and exclude and from which columns of the source sheet. Source sheet is the second work sheet. Any help would be much appreciated

Book2
ABCDEFGH
1PeriodJUL24SEP24
2Costing Work ID21Quaterly
3CODESTransaction TypeEx GSTGST AmountGST Inclusive
4GST Codes - Purchase000
5A0AP INV7261.0907261.09
6A0DR DBT000
7A0AP CRD000
8A1AP INV2523349.49252335.042775684.53
9A1AP CRD-1389.41-138.92-1528.33
10A1DR DBT000
11A1AR ADJ000
12A2AP PPI000
13A2AP INV17602.75017602.75
14A2AP CRD-7190-719
15A4AP INV159.50159.5
16A5AP INV74000740081400
17C1G JNL000
18C1AP PPI000
19S0AR INV-5.23869E-120-5.23869E-12
20S0AR ADJ000
21S0G JNL000
22S0AR CSH000
23S1AR INV1.05501E-0901.05501E-09
24S1AR ADJ-3.63798E-120-3.63798E-12
25S1AR CRD-7.38964E-130-7.38964E-13
26S1AR CSH000
27S1G JNL000
28Total2620264.42259596.122879860.54
290
30Check-81,947.690.000.00
31
32
GST Reconciliation
Cell Formulas
RangeFormula
D30D30=SUMIFS('All Transaction'!$Y:$Y,'All Transaction'!$Q:$Q,'GST Reconciliation'!$D$2,'All Transaction'!$F:$F,'GST Reconciliation'!$D$1:$E$1,'All Transaction'!$K:$K,"<>",'All Transaction'!$C:$C,"<>GSTPaid")
E30:F30E30=SUMIFS('All Transaction'!$Y:$Y,'All Transaction'!$Q:$Q,'GST Reconciliation'!$D$2,'All Transaction'!$F:$F,'GST Reconciliation'!$D$1:$E$1,'All Transaction'!$K:$K,"<>",'All Transaction'!$C:$C,"GSTPaid")


Book2
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Work IdG/L AccountAccount DescriptionBatchLine#PeriodJC PeriodJournal DateTransaction TypeCred/Debt No.Tax CodeInternal ReferenceCreditor/Debtor NameGL DescriptionPurchase OrderPurchase Order ItemCosting Work IdJobCost CodeCost TypeActivityAsset WorkidAsset NoOpening/Closing BalanceTransaction AmountJournal TextExternal Ref/Trust Fund
52121001410Loan to WorkID 10D19561JUL24JUL2430-06-2024AR INVHALGROS1921001525DCOH PTY LTDJun24 Inter ID Expense Allocat2121-1,411.84
62121001410Loan to WorkID 10C03765JUL24JUN2402-07-2024AP INVRICAUSTA115091751RICOH AUSTRALIAE17M750268 CARETAKER JULY 2024219.06
72121001410Loan to WorkID 10C03765JUL24JUN2402-07-2024AP INVRICAUSTA115091751RICOH AUSTRALIAE17M750268 CARETAKER JULY 2024219.06
82121001410Loan to WorkID 10C03765JUL24JUN2402-07-2024AP INVRICAUSTA115091751RICOH AUSTRALIAE17M750268 CARETAKER JULY 2024219.07
92121001410Loan to WorkID 10C03765JUL24JUN2402-07-2024AP INVRICAUSTA115091751RICOH AUSTRALIAE17M750268 CARETAKER JULY 2024219.07
102121001500Trade Debtors ControlD19281JUL24JUL2430-06-2024AR INVHALGROS1921001524DCOH PTY LTDProgress Claim # 1421R230210,501.70
112121001500Trade Debtors ControlD19561JUL24JUL2430-06-2024AR INVHALGROS1921001525DCOH PTY LTDJun24 Inter ID Expense Allocat211,553.02
122121001500Trade Debtors ControlD20111JUL24JUL2429-07-2024AR INVMICPTYS1921001527MICNAN PTY LTDR2301 - 414 - SWEETHEARTS (DUC21R2301414RE28,993.62
132121003310GST CollectedD19281JUL24JUL2430-06-2024AR INVHALGROS1921001524DCOH PTY LTDProgress Claim # 1421-954.70
142121003310GST CollectedD19561JUL24AUG2430-06-2024AR INVHALGROS1921001525DCOH PTY LTDJun24 Inter ID Expense Allocat21-141.18
152121003310GST CollectedD20111JUL24AUG2429-07-2024AR INVMICPTYS1921001527MICNAN PTY LTDR2301 - 414 - SWEETHEARTS (DUC21-2,635.78
162121003310GST CollectedD20031JUL24SEP2431-07-2024AR INVNOVAJVS1921001526Nacap Pty LtdProgress Claim # 721-8,610.70
172121003310GST CollectedD20311JUL24SEP2431-07-2024AR INVHALCONS1921001528DCOH P/L - CONSTRUCTIONProgress Claim # 2221-17,049.30
182121003310GST CollectedD20401JUL24SEP2431-07-2024AR INVSITPTYS1921001530Sitzler Pty LtdProgress Claim # 321-16,266.08
192121003310GST CollectedD20441JUL24SEP2431-07-2024AR INVBARPTYS1921001531BARPA PTY LTDProgress Claim # 821-1,121.92
202121001005Bank-NABC04272AUG24SEP2423-08-2024AP INVALLRECA0DR3765AAALLSKILLS RECRUITMENT & MIGRATREFUND EMER HACHENOVA AIRFARE21540.00
212121001410Loan to WorkID 10D21141AUG24SEP2431-07-2024AR INVHALGROS1921001533DCOH PTY LTDJul24 Inter ID Expense Allocat2121-36.26
222121001410Loan to WorkID 10C04305AUG24AUG2402-08-2024AP INVRICAUSTA115123598RICOH AUSTRALIAE174M750268 CARETAKER AUG 242112.27
232121001410Loan to WorkID 10C04305AUG24SEP2402-08-2024AP INVRICAUSTA115123598RICOH AUSTRALIAE174M750268 CARETAKER AUG 242112.27
242121001410Loan to WorkID 10C04305AUG24SEP2402-08-2024AP INVRICAUSTA115123598RICOH AUSTRALIAE174M750268 CARETAKER AUG 242112.28
252121001410Loan to WorkID 10C04305AUG24SEP2402-08-2024AP INVRICAUSTA115123598RICOH AUSTRALIAE174M750268 CARETAKER AUG 242112.28
262121001500Trade Debtors ControlD21141AUG24SEP2431-07-2024AR INVHALGROS1921001533DCOH PTY LTDJul24 Inter ID Expense Allocat2139.89
272121001500Trade Debtors ControlD21791AUG24SEP2431-07-2024AR INVHALGROS1921001534DCOH PTY LTDProgress Claim # 1521R23028,250.99
282121001500Trade Debtors ControlD21871SEP24SEP2431-08-2024AR INVNOVAJVS1921001535Nacap Pty LtdProgress Claim # 821R230624,640.00
All Transaction
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
For D30 does this get you any closer ?
Excel Formula: 
=SUMIFS('All Transaction'!$Y:$Y,
'All Transaction'!$Q:$Q,$D$2,
'All Transaction'!$F:$F,">=" & $D$1,
'All Transaction'!$F:$F,"<=" & $E$1,
'All Transaction'!$K:$K,"<>",
'All Transaction'!$C:$C,"<>GSTPaid")
 
Upvote 0
This works on my machine:
Note: Your data didn't have any GSTPaid so I changed the first line in All Transactions.

20241205 SumIfs GST Rec ChetanPuri.xlsx
ABCDEF
1PeriodJul-24Sep-24
2Costing Work ID21Quaterly
3CODESTransaction TypeEx GSTGST AmountGST Inclusive
4GST Codes - Purchase000
5A0AP INV7261.0907261.09
6A0DR DBT000
7A0AP CRD000
8A1AP INV25233492523352775685
9A1AP CRD-1389.41-138.92-1528.33
10A1DR DBT000
11A1AR ADJ000
12A2AP PPI000
13A2AP INV17602.75017602.75
14A2AP CRD-7190-719
15A4AP INV159.50159.5
16A5AP INV74000740081400
17C1G JNL000
18C1AP PPI000
19S0AR INV-5.24E-120-5.24E-12
20S0AR ADJ000
21S0G JNL000
22S0AR CSH000
23S1AR INV1.06E-0901.06E-09
24S1AR ADJ-3.64E-120-3.64E-12
25S1AR CRD-7.39E-130-7.39E-13
26S1AR CSH000
27S1G JNL000
28Total2620264259596.12879861
29
30Check27788.66-1411.8426376.82
GST Reconciliation
Cell Formulas
RangeFormula
D30D30=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>", 'All Transaction'!$C:$C,"<>GSTPaid")
E30E30=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>", 'All Transaction'!$C:$C,"GSTPaid")
F30F30=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>")
 
Upvote 0
Thank you Alex, for me i still get 0
V17 BAS Review 05 December 2024.xlsm
ABCDEF
1PeriodJUL24SEP24
2Costing Work ID10Monthly
3CODESTransaction TypeEx GSTGST AmountGST Inclusive
4GST Codes - PurchaseBAS Codes$ 0$ 0$ 0
5A0Not Required For BASAP INV$ 2,060,521$ 10,877$ 2,071,398
6A0Not Required For BASDR DBT$ 0$ 0$ 0
7A0Not Required For BASAP CRD($ (1,623,255))$ 0($ (1,623,255))
8A0Not Required For BASAP PPI$ 29,432$ 0$ 29,432
9A0Not Required For BASAP PPC($ (11,550))$ 0($ (11,550))
10A1G11AP INV$ 25,029,828$ 2,502,983$ 27,532,811
11A1G11AP CRD($ (173,748))($ (17,375))($ (191,123))
12A1G11DR DBT$ 0$ 0$ 0
13A1G11AR ADJ$ 0$ 0$ 0
14A1G11AP PPC$ 1,268$ 127$ 1,394
15A1G11AP PPI$ 552$ 55$ 607
16A2G14AP PPI$ 234$ 0$ 234
17A2G14AP INV$ 248,035$ 0$ 248,035
18A2G14AP CRD($ (50,914))$ 0($ (50,914))
19A4G15AP INV$ 4,280$ 0$ 4,280
20A4G15AP CRD($ (7))$ 0($ (7))
21A5G10AP INV$ 0$ 0$ 0
22C1G1G JNL$ 0$ 0$ 0
23C1G1AP PPI$ 0$ 0$ 0
24S0G3AR INV$ 0$ 0$ 0
25S0G3AR ADJ$ 0$ 0$ 0
26S0G3G JNL$ 0$ 0$ 0
27S0G3AR CSH$ 0$ 0$ 0
28S1G2AR INV$ 0$ 0$ 0
29S1G2AR ADJ$ 0$ 0$ 0
30S1G2AR CRD($ (0))$ 0($ (0))
31S1G2AR CSH$ 0$ 0$ 0
32S1G2G JNL$ 0$ 0$ 0
33S2AR INV$ 0$ 0$ 0
34Total$ 25,514,677$ 2,496,667$ 28,011,344
35
36000
GST Reconciliation
Cell Formulas
RangeFormula
D34D34=SUM($D$4:$D$33)
E34E34=SUM($E$4:$E$33)
F34F34=SUM($F$4:$F$33)
D36D36=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>", 'All Transaction'!$C:$C,"<>GSTPaid")
E36E36=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>", 'All Transaction'!$C:$C,"GSTPaid")
F36F36=SUMIFS('All Transaction'!$Y:$Y, 'All Transaction'!$Q:$Q,$D$2, 'All Transaction'!$F:$F,">=" & $D$1, 'All Transaction'!$F:$F,"<=" & $E$1, 'All Transaction'!$K:$K,"<>")
Cells with Data Validation
CellAllowCriteria
D1:E1List='All Transaction'!$F:$F
 
Upvote 0
In the sample data you sent me there were no records in All Transactions with Costing Work ID = 10 (Column Q), once I changed some IDs to 10 it worked fine for me.
Does the data you are trying it on have data with Costing ID = 10 ?
The next thing to check is to is:
• The date values in D1 & E1 if you change the date format to General does it change to show a number ?
(undo to change it back)
• Do the same with the date column in All Transactions (Column F)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Sumifs with Date Criteria
Replies
2
Views
54
Peter_SSs
Peter_SSs
O
  • Solved
Helpful formatting/delimeters for a multi-use text box?
Replies
4
Views
227
Office_Drone
O
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
758
Fluff
Fluff
M
  • Question Question
Bifurcate total expenses on the basis of calendar months other than AD
Replies
2
Views
505
MandeepBajimaya
M
MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
707
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,224,551
Messages
6,179,470
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top