总结报告的公式

Lisa_Doris

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!

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

@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

. 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 theInvoicecolumn filtering out Invoice Is Blank

Hope that helps.

Ramzi

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

Thank you ! Very insightful and helpful.

Paul Newcome

@RamziThe 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.