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 Newcome
    Paul 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.

  • Lisa_Doris
    Lisa_Doris ✭✭✭✭

    @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

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    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: =Name@row + " payment at " + [Job Address]@row + ", Total= $" + SUMIFS(Total:Total, Name:Name, Name@row, [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, InvoiceInfo@row) = 0, InvoiceInfo@row, "")

    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 - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

    Feel free to email me: ramzi@cedartreeconsulting.com

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.

  • Paul Newcome
    Paul 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, Name@row, [Job Address]:[Job Address], [Job Address]@row)


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

    =IF(COUNTIFS(Name$1:Name@row, Name@row, [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.

  • Lisa_Doris
    Lisa_Doris ✭✭✭✭

    Thank you ! Very insightful and helpful.

  • Paul Newcome
    Paul 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!