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?

-

## migrated from stackoverflow.comJan 13 '13 at 7:19

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

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.

-
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