Formula for summing cells that may be deleted

[Texhun]

I have developed a work order sheet for invoicing maintenance accomplished on
a fleet of aircraft. It consists of a series of 20 block of cells. Each block
of cells is one "item" detailing work accomplished, labor, parts, freight,
total item cost, etc. At the bottom of the sheet I have formulas set for work
order totals. So total labor is: =SUM(I9,I24,I39,I54,I69,I84, and so on until
I294). I have similar formulas for total parts, freight, etc. My problem is
that I want to delete the blocks of cells not used for a particular work
order. So if we used only 8 items, I want to delete item 9 thru 20. When I do
this, my totals formulas at the bottom of the sheet display an error message
because I deleted some of the cells in that formula. The cells I am totaling
are not next to each other so I cannot use a range of cells for the formula.
Is there a way to get around this? Many thanks in advance.

 

[Elkar]

What if you used some sort of identifier in an adjacent cell to each of your
totals. Let's say column H contains the text "Invoice Total:" in each cell
adjacent to where your totals are in column I. You could then use:

=SUMPRODUCT(--(H1:H294="Invoice Total:"),I1:I294)

This would sum everything in column I where the adjacent cell in H contains
"Invoice Total:". That way it won't matter what gets added or deleted.

Or if Column H won't work, just use a blank column and insert an "X" or
something on each of your total lines.

HTH
Elkar

 

[Luke M]

Instead of having your formula pick 'random' cells, is there perhaps
something in each of those particular rows that you could use as a flag?
Perhaps the world total in column H? Then you could just use:
=SUMIF(H9:H294,"Total",I9:I294)
This also makes for a much smaller, manageable formula.

 

[Texhun]

Yes I believe I can do that. I'll give it a try. Thank you so much for your
help!

 

[Texhun]

Yes, I believe I can revise my formula in that maner. I'll give it a try.
Thank you so much for your help!