Summary Report Formula

Summary Report Formula

What is the formula for this scenario:

I have laborers working at different job addresses, I want to calculate the total cost by Laborer Name and by each Job Address in a summary report. Below is the data.

The result I am looking for would look something like this in a summary report for my bookkeeper to easily write checks by job and address. I am required to write separate checks from different bank accounts on each job. No Co-Mingling.

Worker #1 123 Main St Total=$500

Worker #1 1st St Total=250

Worker #2 1st St Total= $400

Worker#3 123 Main St= $200


Is this possible?


Thanks!

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you provide a screenshot of your source data and goal? You can use some basic dummy data manually entered as needed.

    Being able to visualize exactly what you are working with and what you are working towards will help immensely.

  • @Paul Newcome

    The outcome would give me this information in a source report:

    Worker #1 payment at 123 Main St, Total=$250

    Worker #1 payment at 1st St, Total=$125

    Worker #2 payment at 1st St Total, = $300

    Worker#2 payment at 123 Main St, = $200



    Thank you

  • RamziRamzi
    edited 05/23/20

    . Create a new column to the left of the Name column and call it "InvoiceInfo"

    . Add a formula in that column: [email protected] + " payment at " + [Job Address]@row + ", Total= $" + SUMIFS(Total:Total, Name:Name, [email protected], [Job Address]:[Job Address], [Job Address]@row)

    . Next, add another column to the left of that called "Invoice"

    . Add a formula in the column: = IF(COUNTIF(InvoiceInfo2:InvoiceInfo$40, [email protected]) = 0, [email protected], "")

    Note: Adjust the number 40 in the above formula to match the number of rows in your sheet.

    . Then create a report that shows the Invoice column filtering out Invoice Is Blank

    Hope that helps.

    Ramzi


    Ramzi Khuri (www.cedartreeconsulting.com)

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I would use two "helper" columns as well, but I would use different formulas.

    First Helper Column would be [Invoice Amount] (text/number):

    =SUMIFS(Total:Total, Name:Name, [email protected], [Job Address]:[Job Address], [Job Address]@row)


    Second Helper Column would be [For Report] (checkbox):

    =IF(COUNTIFS(Name$1:[email protected], [email protected], [Job Address]$1:[Job Address]@row, [Job Address]@row) = 1, 1)


    The first formula pulls the total you are looking for and the second formula checks the box for each row containing the first entry.


    Building your report based on the checkbox column will remove the dupes and you can leverage the columns you are already using to display the address, name, etc.

  • Thank you ! Very insightful and helpful.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Ramzi The only two concerns I have with your particular solution are...


    If the Name and Location on row 1 is the only entry for that particular Name and Location, it will be missed in the report.


    Additionally... Every time a row is added or removed, the formula in the Invoice column would need to be updated and applied to every row.

Sign In or Register to comment.