# Changing ranges in formulas on the fly

#### RandyTampa

##### New Member
Sorry if this is vague but this is essentially my problem:

I have several sets of zip codes across a state. I want to take groups of zip codes at a time and apply formulas to all the zip codes in a group at a time. The issue is that not all groups have the same number of zip codes.

For example, group A has 100 zip codes and group B has 74 zip codes.
Formulas might involve summing a column C so that in that column
=sum(D1:D\$101) for Group A but for goup B that formula would be sum(D1:D\$75).

Since there are dozens of formulas that are dependent upon a given number of rows across many different tabs, it's not feasible to make individual formula changes for each group.

thanks

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Have you tried using a SUMIF formual?
Dufus

As far as your summation formula goes, as long as the non-used cells are empty, you can sum ALL the rosw. Thus, if column D in Sheet1 has valid entries in the range D1:D101, and, on Sheet2, the range is only D1:D75, the formula you show, =sum(D1:D\$101), would work equally well. Just use a number equal to, or higher than, the largest row you expect.

By the way, your summation formula, I believe, could be written simply as: =sum(D1:D101), without the absolute symbol \$. Just enter it in the same cell on every sheet, say in cell D102, for 101 being the lowest row.

Note that, if you put the summation formula in the first row, with the data range beginning in the second row, your formula could then be: =sum(D2:D102), and you would have the sum always in the same row, row 1.

Thanks

Thanks both of you. I wound up using an indirect reference to the number of rows to be counted/calculated for each formula. I will refer back to what you have posted in case I run into to problmes later.

It was nice to hear that you were able to solve your problem yourself, using a different approach. There are usually various solutions possible, and it is up to the user to select one that works for him, as in your case. Congratualtions!

It was nice to hear that you were able to solve your problem yourself, using a different approach. There are usually various solutions possible, and it is up to the user to select one that works for him, as in your case. Congratulations!

Replies
5
Views
79
Replies
12
Views
169
Replies
3
Views
241
Replies
4
Views
271
Replies
3
Views
2K

1,214,773
Messages
6,121,460
Members
449,033
Latest member
Kcolwell16

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back