Division with a divisor as a dynamic range.

[vadius]

Hi

I am trying to calculate a % weight of a stock within a basket (weight basket / sum of the weights of the components of the basket)
My concern is about the range of cell I need to select to calculate the sum of the weights.
See attached example

In sheet "weight"
column A : I enter some dates.
column B, D : Given the entered dates, I retrive the data (tickers) fron the sheet("data"). Note than by changing the dates, the data will change.
column G: what I am trying to calculate.
For each ticker, I want to calculate weight / ( sum of the weights of all the components for the date 31/12/2012). For example for BKIA SM Equity, in G4 I do F4/Sum(F4:F12) . And like that for all entry with date = 31/12/2012
Then I move to the following date : for 29/06/2012. In cell G14 = F14/ Sum (F14:F20) ...

The issue I have is that the range of dates will move. If I don't enter "31/12/2012" in A4 for example, I can t manage to make excel calculate the right weights, hereby taking the sum of the corresponding dates. In the example, delete the date, and you will see: Now G4 = F4/ SUM(F4:F13)
The formula in the excel are with $ to simplify when I computed the example. And I want to have G4= F4/ SUM (F4:F10)

If you have ideas on how to change automatically the range of cell used to compute the divisor (sum of weights) don't hesitate to let me know..

thanks

[rcm]

look at column H of the modified version of example:

I used SUMIF(range;criteria;sumcolumn) the criteria being the dates..

[vadius]

Thanks, that's exactly what I want. SUMIF was the right one.