How do I extract a monthly summary of CREDIT & DEBT amounts from my bank statement in Google Sheets?

Question

Suppose I have the following type of data in google sheets (Date format is English DD/MM/YYYY) It reflects a typical bank statement going back 10 years (so thousands of such debit/credit entries)

| A | B | C | D

Date	Debit	Credit	Closing Balance
16-12-2023	0	50	100
24-12-2023	20	0	80
27-12-2023	0	10	90
29-12-2023	15	0	75
01-01-2024	0	30	105
12-01-2024	5	0	100
24-01-2024	0	10	110

I want to be able to scan the data row (A) and extract monthly summery of how much debit and credit is being used. So ultimately I want to have a create a new series of tables which have the following report. It should add all up.

Date	Debit	Credit
Dec-2023	35	60
Jan-2024	5	40

How would I go about doing this?

I have tried using sumif() but how do I deal with the conditional debit and credit columns?

Answer 1

You may try:

=query({index(if(len(A2:A),eomonth(A2:A,),)),B2:C},"select Col1,sum(Col2),sum(Col3) Where Col1 is not null group by Col1
        label Col1 'Date',sum(Col2) 'Debit',sum(Col3) 'Credit' format Col1 'mmm-YYY' ")