Dynamic Excel Sumifs/Countifs/Averageifs Formulas

[zrobinson101]

I am looking to build a dynamic set of formulas that summarize Monthly and YTD information without having to update the formulas. For example, say I own a portfolio with several stores in the retail, food and services respectively industries and have a database that breakout daily sales for each of these stores. One example of the formula I want to create is an averageifs formula that would average all the daily sales for all the retail stores in my portfolio within that month's date range but I would have the ability to add in another store in the dataset and not have to update the parameters of the averageifs formula.

 

[ravishankar]

If I have fruits in col A and prices in col B I would define names for fruit with the formula =offset(sheetname!$A$2,0,0,counta($A:$A) and price with =offset(sheetname!$B$2,0,0,counta($B:$B) . cell C1= averageifs(price, fruit, "Apple") gives the mean price of apples. you can add as many rows of data as you choose.
ravishankar

 

[steve the fish]

Sounds like a job for the pivot table to me.