Summarizing Expenses by Committee

Hi Everyone

I’m the Treasurer for a homeowners association. I’m tracking all check we write this year by General Ledger account in the “Raw Data” worksheet of the attached workbook. The Raw Data includes every check written this year, even though some of the expenses were incurred last year.

I would like use formulas to build a report for each committee in the form shown in the “Desired Output” worksheet. This committee is responsible for expenses in seven General Ledger accounts as shown. I would like formulas in this worksheet that will show:

* Only expenses for one of the indicated General Ledger accounts for this committee.

* All of the columns shown in the Desired Output worksheet for each check. (Date, Payee, Invoice, and amount in the appropriate column)

* Sorted in the order they appear in the Raw Data worksheet.

* Exclude any expenses for last year (date in 2020). These dates are highlighted in yellow in the Raw Data.

* Some entries will be negative numbers to represent refunds or credits. These need to be included in the Desired Output.

One I get this Desired Output sample working, I should be able to adapt it to our other committees.

Please let me know if you have any questions after reviewing this information.

Thanks in advance for all assistance!

Desired Output

D5=IF('Desired Output'!$A5<>"",SUMPRODUCT(('Raw Data'!$F$4:$BA$4='Desired Output'!D$4)*('Raw Data'!$A$5:$A$400='Desired Output'!$A5)*('Raw Data'!$D$5:$D$400='Desired Output'!$C5),('Raw Data'!$F$5:$BA$400)),"")

Copy across and down

for hide 0 custom formats [=0]""

Thank you for your fast reply!

This is not quite what I am looking for. I won't know in advance the date, payee or invoice number (columns A, B and C in the Desired Output), and I don't want to look through all the raw data to enter these values manually. I need these columns to be populated by formula as well.

Sorry my original post was not more clear.

I changed the position of invoice in the rowdata sheet to use only one formula

Desired Output

A5=IFERROR(INDEX('Raw Data'!A$5:A$400,AGGREGATE(15,6,ROW(A$5:A$400)-ROW('Raw Data'!A$5)+1/ISNUMBER(MATCH('Raw Data'!$F$4:$BA$4,'Desired Output'!$E$4:$K$4,0))/(ISNUMBER('Raw Data'!$F$5:$BA$400))/(YEAR('Raw Data'!$A$5:$A$400)=2021),ROWS('Desired Output'!$A$5:'Desired Output'!A5))),"")

Copy across and down until invoice


D5=IF('Desired Output'!$A5<>"",SUMPRODUCT(('Raw Data'!$F$4:$BA$4='Desired Output'!D$4)*('Raw Data'!$A$5:$A$400='Desired Output'!$A5)*('Raw Data'!$D$5:$D$400='Desired Output'!$C5),('Raw Data'!$F$5:$BA$400)),"")

Copy across and down

for hide 0 custom formats [=0]""

Bo_Ry and CARACALLA, thank you for these replies! I'll play with them and post back when I either have them working in my real data or need additional help.

CARACALLA, your second suggestion is better but still has some issues.

I have several dates with multiple invoices from the same vendor. Instead of showing each invoice separately, it is totalling all invoices from that vendor on that date. And it is showing that total on the row for each invoice.

I tried modifying your sumproduct formulas to search for invoice numbers. This is better, but some invoices do not have invoice numbers, so the blank invoice numbers are being totaled together.

I was able to modify your aggregate formulas to pick out the values in every column, without using sumproduct.

Thanks again very much for your help!