Summary Report Formula

Options
✭✭✭✭

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!

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 05/23/20
Options

. 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

• ✭✭✭✭✭✭
Options

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

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

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

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.

• ✭✭✭✭
Options

Thank you ! Very insightful and helpful.

• ✭✭✭✭✭✭
Options

@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!