Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

1 Answer 1

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.