Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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