subtotal of sumproducts?

**Alanmac** · 05-14-2012, 07:04 AM

I have a list of data that I can filter via a number of variables. I currently have subtotals for each column of numerical data, the subtotal value autosums the filtered data I have selected.
I also have a sumproduct funtion for each column of numerical data to incorporate a probability of between 0-1 for each cell. The problem I have is when I filter the data, the sumproduct funtion does not change accordingly?
Is there a way to combine the subtotal and sumproduct funtions so that I get an autosum based on teh filtered selection?

Here is the detail

Column A = Region
Column B = Country
Column C = Probability (Between 0-1)
Column D = Month 1
Column E = Month 2

Required detail:
Sumproduct of Column C & Column D (by region and or country filter)
Sumproduct of Column C & Column E (by region and or country filter)

Thanks in advance for your help

**Fotis1991** · 05-14-2012, 07:57 AM

Hi

I am sure, that this will help you.

http://www.excelforum.com/excel-work...ered-data.html

**canapone** · 05-14-2012, 07:59 AM

Hi,

in E2

=SUMPRODUCT(SUBTOTAL(9,OFFSET(E$3:E$1000,ROW(E$3:E$1000)-MIN(ROW($A3:$A999)),,1)),--(B$3:B$1000="A"),--(C3:C$1000="X"))

In the sumproduct

--(B$3:B$1000="A"),--(C3:C$1000="X")

are the two conditions to match in the filtered data; you can add other conditions.

Hope it helps

Regards