5

I'm trying to set up a budget workbook for my personal budget using 13 sheets, 1 for the totals and the other 12 for each month. I cannot for the life of me figure out why the formulas I have change when I insert a row into one of the monthly sheets. Here's an example of one of the formulas I have:

=SUMIF(JUN!$G$2:$G$500,"Utilities", JUN!$D$2:$D$500)

If I insert a row at the top of a sheet, it will increment the twos to threes, throwing off the calculations. Is there any way I can lock the formula from changing at all? It's incredibly frustrating.

  • 1
    WHat happens if you use =SUMIF(JUN!$G$1:$G$500,"Utilities", JUN!$D$1:$D$500)? Presumably row 1 is collumn headers and will thus never be used by the SUMIF and if you then insert a row between rows 1 and 2 the formula will still be fine – cybernetic.nomad Jul 1 '18 at 22:22
  • @cybernetic.nomad I'll tell you what happens. It will just work! Like magic ;-) (Unless, of course, the header in G1 is Utilities and the header in D1 is a number. In which case you'll get everything you deserve :-D) – robinCTS Jul 2 '18 at 3:11
  • @cybernetic.nomad That worked for me and was the easiest solution in my case. It still changes increments the 500, but whatever, it was arbitrary anyway – Mike Jul 2 '18 at 22:20
6

What you need to understand is that the absoluteness of absolute references, as specified by the $, is not absolutely absolute ;-)

Now that that tongue-twister is out of the way, let me explain.

The absoluteness only applies when copy-pasting or filling the formula. Inserting rows above, or columns to the left, of an absolutely referenced range will "shift" the address of the range so that the data the range points to remains the same.

In addition, inserting rows or columns in the middle of the range will expand it to encompass the new rows/columns. Thus to "add" a row of data to a range (table) you need insert it after the first data row.

The simplest way to allow adding a data row above the current data range is to always have a header row, and include the header row in the actual range. This is exactly the solution proposed by cybernetic.nomad in this comment.


But, there's still one more issue left, and that's adding a row of data after the end of the table. Just typing the new data in the row after the last row of data won't work. Nor will inserting a row before the row after the last row.

The simplest solution for this is to use a special "last" row, include that row in the data range, and always append new rows by inserting before that special row.

I typically reduce the row height and fill the cells with an appropriate colour:

Worksheet Screenshot

For your example, the full "simplest" formula would thus be:

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)


Another way to achieve the same goal is to use a dynamic formula that auto adjusts to the amount of data in the table. There are a few different variations of this, depending on the exact circumstances and precisely what is to be allowed to be done to the table.

If, as is typically the case (your example, for instance), the table starts at the top of the worksheet, has a one row header, and the data is contiguous with no gaps, a simple dynamic formula would be:

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

This is a better solution than using INDIRECT() as

  1. It is non-volatile and therefore the worksheet calculates faster, and
  2. It won't break if you insert columns to the left of the table.

The dynamic formula technique can be further improved by using it in a Named Formula.



Of course, the best solution is to convert the table to a proper Table, and use structured references.

2

So you’re saying that, if you insert a new Row 2 (between the current Row 1 and Row 2), you want the formula to look at the new Row 2?  Here are a couple of variations:

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

will always look at Rows 2 through 500, without regard to rows being renumbered by insertions (or deletions).  This means that, if you insert a row, the original Row 500 will be renumbered to 501 and will be bumped out of the range.  If you want to look at the current Row 2 through the original Row 500, use

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

In case it isn’t obvious, INDIRECT() takes a string (text) argument and interprets it as an address.  It lets you do invariant addressing, because the strings (that look like addresses) won’t get adjusted when other addresses get adjusted because of row/column insertion/deletion.

Note that the $ characters in the address strings are optional; they have no effect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.