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

[dflak]

First it would help a lot if you included a sample workbook.

However, based on what I am reading, it's possible that Excel Tables may help you out. See this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Secondly, depending on how you have the data organized, a pivot table might do the job for you.

[nimrod]

Have a column for customers , a column for fruits , a column for Town , a column for fruit quantity
Ditch the formula
Now use the sheet filters so show by fruit OR by customer OR by Town ... or a combination of them
If you look at bottom of excel frame you will automatically see the max, ave , count , totals etc ... this will be easier and more flexible
... and no formula !

[Cdubisms]

I'd go with a combination of dflak's suggestion. I'd make it an Excel Table and then have pivot table based off that table. As you add entries to the table, the pivot table source data remains dynamic.

[Pete_UK]

An alternative approach - instead of using SUM, you can use SUMIFS, and then you can use full-column references. With SUMIFS, you can set up one or more conditions which govern which cells to add, effectively 'add cells from column I where the location column contains "Jasper, Alberta"', so it doesn't matter where the records for customers from that town actually occur.

Hope this helps.

Pete