calculation amounts for BANK & CASH across sheets based on matching partial item

Hi
the result should be in G2,G3 for BALANCES sheet.
in column (RECEIVED/PAID/NOT PAY) as in header for each sheet contains BANK or CASH should merge amounts are existed in last column with exclude AGGREGATE row from calculation across sheets first .
second should subtract the merged amounts from each other of them (RECEIVED -PAID) for BANK or CASH .
after that should sum the balance to B2,B3
example:
when merge RECEIVED for BANK for three sheets(SL,RS,VC)
RECEIVED BANK=40400+13400+480+120+200=54600
when merge PAID for BANK for four sheets(PR,RP,EP,VC)
PAID BANK=15000+2620+1100+1100+11500+1500+200+200=33200
after that should be 54600-33200=21380
final step when show the result in G2 for BANK should add to B2 will be like this=
21380+200000=221380 as show in G2
the same thing for CAH whether PAID or RECEIVED.
also posted in this forum
https://www.mrexcel.com/board/thread...-item.1257716/
if it's my way is really comlicated I accept any idea to make easy th solution.
any solution to do that will be great.

Your data in the file is inconsistent/wrong :

when merge RECEIVED for BANK for three sheets(SL,RS,VC)
RECEIVED BANK=40400+13400+480+120+200=54600

SL/SR do not have "RECEIVED BANK" only "PAID BANK" whereas VC has "RECEIVED BANK"....

when merge PAID for BANK for four sheets(SL,RS,EP,VC)
PAID BANK=15000+2620+1100+1100+11500+1500+200+200=33200

These figures do not agree with your file .....

"PAID BANK" in SL are 40,400 and 13,400

I don't see any diffrence in the file if it has been edited.

Sheet "SL" is NOT changed.

Option Explicit
Sub demo()
Dim WS_Count As Integer, i As Integer, j As Integer, pCol As Integer, tCol As Integer, lr As Long
Dim ShtArr, PaidArr, x
Dim pr(1 To 4)  As Double
Dim ws As Worksheet
Dim wsn As String
ShtArr = Array("SL", "RS", "VC", "PR", "RP", "EP")
PaidArr = Array("RECEIVED BANK", "RECEIVED CASH", "PAID BANK", "PAID CASH")

Application.ScreenUpdating = False

For i = 1 To 6

    Set ws = Sheets(ShtArr(i - 1))
    
        With ws
            pCol = Application.Match("*" & "PAY", .Rows("1:1"), 0) ' Find column with "PAY" header
            tCol = Application.Match("TOTAL", .Rows("1:1"), 0)     ' Find "TOTAL" column
            If i <= 3 Then
                For j = 1 To 2                                        ' Loop through RECEIVED                      x
                     pr(j) = pr(j) + WorksheetFunction.SumIfs(.Columns(tCol), .Columns(pCol), PaidArr(j - 1) & "*")
                Next j
            End If
            If i >= 3 Then
                For j = 3 To 4                                        ' Loop through PAID
                     pr(j) = pr(j) + WorksheetFunction.SumIfs(.Columns(tCol), .Columns(pCol), PaidArr(j - 1) & "*")
                Next j
            End If
            
        End With
    
Next i

With Sheets("BALANCES")
    .[G2] = .[b2] + (pr(1) - pr(3))   ' BANK
    .[G3] = .[B3] + (pr(2) - pr(4))   ' CASH
    .[G6].Resize(4, 1) = Application.Transpose(pr) '  <<<< for TESTING only
End With

Application.ScreenUpdating = True

End Sub

Why do some entries have PAYED and some PAID?

I'm guessing it is an error and all should be PAID...

Sub J3v16()
Dim ShtArr, Bnk As Double, Csh As Double, Col1 As Long, Col2 As Long, i As Long
ShtArr = [{"SL","PR","RS","RP","EP","VC"}]
For i = 1 To UBound(ShtArr)
    Col1 = IIf(i = 6, 5, 8): Col2 = IIf(i = 6, 4, 5)
    With Sheets(ShtArr(i))
        Bnk = Bnk + WorksheetFunction.SumIfs(.Columns(Col1), .Columns(Col2), "RECEIVED BANK*")
        Bnk = Bnk - WorksheetFunction.SumIfs(.Columns(Col1), .Columns(Col2), "PAID BANK*")
        Csh = Csh + WorksheetFunction.SumIfs(.Columns(Col1), .Columns(Col2), "RECEIVED CASH*")
        Csh = Csh - WorksheetFunction.SumIfs(.Columns(Col1), .Columns(Col2), "PAID CASH*")
    End With
Next i
Range("G2") = Range("B2") + Bnk
Range("G3") = Range("B3") + Csh
End Sub

Glad to have contributed...Tx for rep +

Thanks fo the rep