How to use SUMPRODUCT to list all the instances of journal entries

English
, , ,
#1

I am working to create a simple accounting application, composed of a monthly journal entry sheet (MJE), a monthly detailed journal ledger (DGL) sheet, and a monthly balance or financial statement (FS).

I have been trying to use ChatGPT to teach myself the basics of some of the entry summation and listing, but my free level reached its limit and I lost all of my notes.

I am uploading a copy of my workbook. My current goal is to list the journal entries according to account number. The chart of accounts, or the listing of account numbers is on the FS page. The listing of all journal entries is on the MJE page. I think I am able to retrieve the account number and name from the FS sheet and put it into the correct cells in the DGL sheet, but I have forgotten how to extract a listing of journal entries from the MJE sheet, place them in an account section on the DGL sheet, and cause the operation to iterate through all of the accounts listed on the FS sheet.

Can anyone help?

Thanks,
Kirk
Cash Book- example - WORKING-2411115 a.ods (29.0 KB)

#2

Don’t understand why INDIRECT is used in the formula.
I think easy also in this case use SUMIFS instead SUMPRODUCT
=SUMIFS($MJEJAN.$H:$H;$MJEJAN.$B:$B;$A12;MJEJAN.$F:$F; TEXT(B12; "0"))
set up properly the criteria.
BTW in your formula inside INDIRECT you are using MJE-JAN while the sheet name is MJEJAN.

#3

Thanks MarioSV, but I cannot get it to work, even after removing the dash from the sheet name.

#4

But now you have no error in the result. So it must be a question of right criteria in the formulas. Specially, I don’t understand the last criteria TEXT(B12; "0") what you want to search with it?

#5

The TEXT(B12,“0”) causes the format of the B12 cell to be recognized as a date format which we use to identify the month by keeping LO from changing it to the numeric date, say 40932.

#6

But in your sample file it references to B8 instead and to column F which is blank.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.