Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have the following formula in the top cell:

=SUMIF('Jan ''13'!D1:D165, A2, 'Jan ''13'!B1:B165)

I need the next one to be:

=SUMIF('Jan ''13'!D1:D165, A3, 'Jan ''13'!B1:B165)

Unfortunately, Google auto fills this to:

=SUMIF('Jan ''13'!D2:D166, A3, 'Jan ''13'!B2:B166)

As you see, Google auto fills every value. Is there a way to limit this to certain ranges?

share|improve this question

migrated from Jan 13 '13 at 7:19

This question came from our site for professional and enthusiast programmers.

up vote 1 down vote accepted

You can add $ to either the row or the column (or both) to make it stay constant in a formula for autofilling.

You want this:

=SUMIF('Jan ''13'!$D$1:$D$165, $A2, 'Jan ''13'!$B$1:$B$165)

This formula would only increment the row part of the $A2 of your formula. Everything else would stay the same.

From the looks of your question, the $ before the column markers in my answer aren't strictly necessary, but I put them there just in case you decide to fill left/right instead of just up/down.

If you experiment with a bit, you'll find that you can any of these is valid for a cell: $D$1 or D$1 or $D1 or D1. This applies to both the starting and ending cell for a range.

share|improve this answer
THANK YOU SO MUCH! You just saved me a few hours of my life :) – Uno Mein Ame Jan 13 '13 at 0:52
@UnoMeinAme Glad I could help. :) – Duotrigesimal Jan 13 '13 at 1:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .