Maintain cell reference when inserting a row.

[Tedejr]

I use the following formula---sumif(f2:f10,"=10/1/2019",G2:G10). Proceeded by the equal sign of course. The purpose is to look for any values in Column F that equal 10/1/2019, and then sum all of the values in the corresponding rows in column G. Here is my problem though. Often I need to insert one to several rows at row 2. When I do so, the f2 and g2 will increment by the number of inserted rows. So, if I insert two rows, F2 becomes F4.

Is there a way to keep "F2" constant? If you have a solution, please show me the precise syntax for my formula in your reply. I understand better that way.

Thanks

[Tedejr]

BTW, I just discovered a potential work around. Of course, I thought of it after posting. If I insert the rows starting at row 3 instead of two (Row 1 is occupied by headers), then the F2 and G2 remains constant. However, the F10 and G10 will increment. So, this is not a complete work around.

[JeteMc]

Hello Tedejr and Welcome to Excel Forum.
I am not sure that I understand the problem. It would seem that if you add a row after row 2, then you would want the formula to increase the range i.e. =SUMIF(F2:F11,"10/1/2019",G2:G11).
However if you want to restrict the range to rows 2:10 then try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Tedejr]

Yes, the goal is to keek the selection fixed.. So, I may try the "indirect" solution in a few days. At the beginning of each month the workbook updates. So I don't want to possibly muck anything up so close to that point.

What was happening though was that if I inserted a row at row two, then the formula would change from F2:F10 to F3:F11. Loosing the data from row two. And that was unacceptable. My workaround up to this point was to add a row at row 3. That would change the range from F2:F10 to F2:F11. Which for me is okay. As long at maintains a reference to row two.

Thanks for the response. I will let you know what will happen after that.

[JeteMc]

Based on the description I don't believe that the INDIRECT formula is going to do what you want as it will not sum data except that in the range G2:G10 even if the actual data extends further.
It would seem that the formula could be written: =SUMIF(F1:F10,"10/1/2019",G1:G10)
Presumably F1 will be a header such as Date so G1 would not be included in the summation anyway.
Now if you insert new rows at row 2 you should still get the expected result.
Let us know if you have any questions.