How to safeguard my sum formula from additional rows I will need to add in future?

[JonDeuling]

Hi there,

My rows are my customers and their fruit orders (I retail fruit). My columns are my fruit items. I my customers divided by the towns in which I visit them.

For each town, I want to have the total number of boxes of each fruit item which they order. I have figured out how to do this, but my issue is that although I have a neat sum function going from I6:I123, there will inevitably be new customers whom I will add. When I happened to add another customer row under fruit column I, just to check to see if there would be issues, the function did not capture that customer #124. Hence I have issues!

How can I make sure that all customers in Jasper, Alberta (those are customers in rows 6:123, currently) will all be accounted for in the sum function, even when I add customers beyond row 123?

Thanks,
Jonathan

[dosydos]

One way of doing this is having one blank row at the bottom of your data.
ex. You have data in I6:I123 create a blank row in I124 and then have your sum formula =sum(I6:I124) when you add a new vendor you just click on the blank row and insert a new row so that there is always one blank row in between your data and the formula. Your formula will update automatically.

[djbomaha]

Hi Jonathan,

You could try capturing the entire column I if everything above line 6 is just text or blank so it wouldn't affect the total. The function would be:

Please Login or Register  to view this content.

Otherwise you could just pick a large number for the last row, like 1000:

Please Login or Register  to view this content.

Hope that helps,

Dan

[loginjmor]

Hi -

You could use the OFFSET function as follows:

=SUM(OFFSET($B$1,,,COUNT(B:B),1))

This dynamically changes the range that you sum as you add more data points. Assuming the data you want to sum starts in cell B1, the COUNT function counts how many numeric entries there are in the column and returns that as the number of rows to sum. Note, COUNT does not count blanks. So if you have intervening blanks in the column, that will reduce the number of rows that get summed.

Without a sample of your data set, this is about all the help I can provide.