Estimate Form - Summing totals when cells data moves when adding rows

[darenferg]

I'm creating a part and labor estimating worksheet. The worksheet can grow in rows when additional parts lines and labor lines are added. I'm looking for a formula that can sum the total labor and parts dollar totals in a worksheet. I can't figure out the formula when the labor and parts cells move or more lines/rows are added.

Thanks
Daren

[oeldere]

Not an answer to your question, but

1 serious good advice.

Get rid of the merged cells, you get in trouble with it sooner or later.

In your case I suppose that will be sooner.

[Limor_OP]

First of all try not to use merged cells like my former suggested.
you can try this for Labor/parts respectively:


=SUMPRODUCT(IFERROR(INDEX($L$1:$L$31,(ROW(L10:L31)/(LEFT(L10:L31,5)=$N$34))+1),0))


=SUMPRODUCT(IFERROR(INDEX($M$1:$M$31,(ROW(M10:M31)/(LEFT(M10:M31,5)=$N$35))+1),0))

[torachan]

As an alternative, attached application given to a query on this site a couple of years ago.
No on-sheet formula required, tables contain Customer details, labour & materials ( small orange button (upper left) activates a small input form.
On the Invoice form data is only entered or selected in yellow boxes - start by pressing New Invoice button.
Selecting VIN will fill Customer/Vehicle details - then select Part Nos followed by lower two boxes for labour.
If this approach is of interest the attached can be adapted to your requirements.
Also just to reinforce other members comments - please do not use 'merged cells' they were invented by the 'devil' to frustrate the unwary.

[darenferg]

Thank You Belinda, it works perfectly.

I appreciate the heads up on the merged concern potential problems.

Thanks,
Daren