Using AutoFilter + sumproduct formula
I have a spreadsheet that has a column that has A and B. It also has a column
that contains a number and another column that contains a %
I have weighted average at the bottom of spreadsheet.
Example:
col A | col B | col C
A-----|---35-|--3.5%
B-----|---49-|--2.7%
B-----|---23-|--1.5%
Average %= x<---------- (sumproduct = (B1:B3,C1:C3)/Sum(B1:B3)
What I need to do is have an autofilter that will filter Column A to show
either All, "A" or "B". When I do this the average formula does not change to
the filtered cells.
I do not know how to use a formula that will select the range for only the
visible (filtered) cells.
In short, how can I do a weighted average for a range of cells using an
autoflter.?
Thanks for any help that you may be able to provide.
|