Lock sumif to specific cell range

Question

I have a spreadsheet that contains data in columns A:D. Each block of data A1:D11, A12:D22, etc. represents a single days worth of data. It has configuration items that I do a sumif function on to pull for the grand total since the report began(=SUMIF(A:A,L2,B:B). I have now been asked to sum only the last weeks worth of data. My current formula is (=SUMIF(A1:A55,L13,B:B)-very simple. However, I need it to lock so that we only checks data between A1:A55. If I add in a new days block of data it changes the reference to (=SUMIF(A12:A66,L13,B:B). If I use the $ refernce it does the same thing...how do I lock the formula so that it will always look between A1:A55?

Regards

Answer 1

When you do some deletions or insertions, excel will shift the reference used in the formula automatically. e.g. If the original formula in B1 is =SUM(A1:A20), after inserting cell A5 with shifting cells down , the formula will be =SUM(A1:A21).

To avoid this situation, you can use the formula INDIRECT, it will return the reference decided by the argument with string type. Since it is a string, excel will not change its value, therefore the range will always be the same no matter you inserted or deleted something.

So give it a try =)

=SUMIF(INDIRECT("A1:A55"),L13,B:B)

Answer 2

As an alternative suggestion, I would create another column in your table that calculates the weeknumber from the date using weeknum() and then use sumif to lookup a specific week.

That way you don't need to worry about row inserts at all.

By using the Indirect() method (which is a good solution and may be be all you need to solve your problem), you still have the problem of data being inserted into the range that your sumif() covers that might not be part of the week that you're monitoring (for example, if a row containing data from a different week were to be entered into A55).