SUMIF question - again

[Glen Mettler]

I have the following formula to calculate some numeric data:
=SUMIF(F6:F27,F5,S6:S27) (this is in cell S5)
the qualifying code is in column F and the numeric data to sum is in column
S

Problem. When I insert a row just below row 5, the formula in S5 changes
to:
=SUMIF(F7:F27,F5,S7:S27)

I will be adding data to the new row6 and I need it to become a part of the
calculation.
I need the formula to remain F6: and S6:

Peo Sjoblom gave me this fix:
=SUMIF(INDIRECT("F6:F27"),F5,INDIRECT("S6:S27"))

I can't use it. Here is a further explanation:
I have criteria that engineers enter tasks for. They enter a task via a
menu option I have created. A macro enters a row and populates various
cells with formula and finally places the cursor in the Description column
for editing/creating by the engineer.
After creating the description of the task, the engineer enters hours for
the task (column S). The engineer can enter several tasks to support a
selected criteria.

Therefore, I need the formula to be dynamic downword but the original
formula in the criteria to remain the same.
So if Criteria 1 has formula: =SUMIF(F6:F27,F5,S6:S27) (cell S5), it needs
to remain so after any number of task entries below it (say 3)
Criteria 2 would have an intial formula of =SUMIF(F7:F27,F6,S7:S27) (in
cell S6) prior to adding rows. After rows are added for Criteria 1,
Criteria 2 (now row 9) should have an adjusted formula of
=SUMIF(F10:F27,F9,S10:S27)

Is this possible?
(Hard to explain and visualize w/o a working workbook)

Glen

 

[Ken Wright]

What's wrong with the answer that Peo gave you in your original post?
Please stick with the original post to avoid fragmenting answers.

 

[Harlan Grove]

Glen Mettler wrote...

I have the following formula to calculate some numeric data:
=SUMIF(F6:F27,F5,S6:S27) (this is in cell S5)
the qualifying code is in column F and the numeric data to sum is in column
S

Problem. When I insert a row just below row 5, the formula in S5 changes to:
=SUMIF(F7:F27,F5,S7:S27)

I will be adding data to the new row6 and I need it to become a part of the
calculation. I need the formula to remain F6: and S6:

....

There's a simple fix, but it requires a modest reformatting of your
worksheet. Move the original data that starts off in F6:S27 to F7:S28,
but change your formula to

=SUMIF(F6:F29,F5,S6:S29)

Now add text to F6 like 'Insert below this row', center it across the
range F6:S6, and give F6:S6 different background and text colors than
the cells in F7:S28. Copy F6:S6 and paste into F29:S29. Replace the
text in F29 with 'Insert above this row'.

Now you can insert (and delete) rows at will *between* rows 6 and 29
and your formula will adapt to include all rows between these rows of
labels. This is a VERY OLD spreadsheet trick, and it works as long as
you don't delete the label rows or cut from elsewhere and paste into
them.

 

[Harlan Grove]

Glen Mettler wrote...
....

Therefore, I need the formula to be dynamic downword but the original
formula in the criteria to remain the same.
So if Criteria 1 has formula: =SUMIF(F6:F27,F5,S6:S27) (cell S5), it needs
to remain so after any number of task entries below it (say 3)
Criteria 2 would have an intial formula of =SUMIF(F7:F27,F6,S7:S27) (in
cell S6) prior to adding rows. After rows are added for Criteria 1,
Criteria 2 (now row 9) should have an adjusted formula of
=SUMIF(F10:F27,F9,S10:S27)

....

So what should the formula be after 3 tasks were added? If it should
look like

=SUMIF(F6:F30,F5,S6:S30)

then see my other response. On the other hand, if it should look like

=SUMIF(F9:F27,F5,S9:S27)

then try

=SUMIF(F9:INDIRECT("F27"),F5,S9:INDIRECT("S27"))

 

[Glen Mettler]

I don't understand. Do you have a sample I could see?

Glen

 

[Harlan Grove]

I don't understand. Do you have a sample I could see?

....

No. Follow my instructions and you'll have a live example.
....