SUMPRODUCT but using a filter?

[sjm]

Hi

I have a list of regions, with corresponding sales figures and an IF formula to determine if there is growth or decline.

I need to have one totals section at the top of the page that will calculate the sums depending on which region you filter.

Standard totals of the sales figs are fine but I also need to sum all the growth figs and all the decline figs (based upon the IF formula I have used), without filtering them separately.

I want the totals section to show total growth and total decline at the same time, (wth all the correspoding date further down the page) but change depending which region you filter to. If you just SUMPRODUCT it calculates the total and this doesn't change if you filter the region.

Has anyone got any ideas how I can achieve this???

Many thanks

[Carim]

Hi,

The easiest is to use the UDF FilterCriteria to get back your region
http://www.j-walk.com/ss/excel/usertips/tip044.htm

and incorporate it in your sumproduct formula ...

see example from a previous post
http://www.excelforum.com/showpost.p...1&postcount=27