SUMPRODUCT with Range Determined by Formula

Hi,

In cells B2, B6 and B10 I have formula that calculates weighted-average growth rates per country.

The formula works in such a way that the weighted average excludes products in a country for which the Growth Rate is "n/a".

When creating the formula for each country I have manually selected the data range for each. However, in reality the table has far more countries and products than in my sample.

Can someone please suggest how to adjust the formula in B2 so that it will use the country indicated in column D to decide which products to include in the calculation for each country, rather than me having to select the range for each country?

Thanks!

It's not ideal to have the formulas in the same column as the data. That will generate circular references if the formula is meant to look at the whole column.

My suggestion is to use a helper column with the following formula, for example in F2, copied down.

Please Login or Register  to view this content.

Then you can use a simple

Please Login or Register  to view this content.

Now you would still have to manually copy and paste that formula into the rows with the totals. Not ideal.

You may want to consider using Pivot Tables to avoid such messy manual interaction.

In a separate column (Not ideal to use Column B) use in Row 2

=IF(D2="",SUMPRODUCT(--(D$3:D$13=A2),B$3:B$13,C$3:C$13/SUMIFS(C$3:C$13,D$3:D$13,A2,B$3:B$13,"<>"&"n/a")),"")

Copy all the way down