Hi All,
I'm looking to create a pivot table (or hell, I'll take a straight spreadsheet formula) that utilizes sumproducts, or a variation formula of, that will allow me to filter out data and automatically update my sumproduct to exclude the filtered data. In the chart below, to get the data I need [which, the main detail I need is C5 which would have the calculation of sumproduct(B1:B4,C1:C4)/B5 ] I would want to be able to filter out Product Y and get an updated weighted average based on the remaining data.
I have the below formula, which works fine when it comes to a straight total( for example, in the chart I provided the reviews come to a total of 20941) however this doesn't work for the data I need when a sumproduct is in play (such as the 88.99)
=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,{0;1;2;3},)),$B$2:$B$5)
Any direction that can be provided would definitely be appreciated.
I'm looking to create a pivot table (or hell, I'll take a straight spreadsheet formula) that utilizes sumproducts, or a variation formula of, that will allow me to filter out data and automatically update my sumproduct to exclude the filtered data. In the chart below, to get the data I need [which, the main detail I need is C5 which would have the calculation of sumproduct(B1:B4,C1:C4)/B5 ] I would want to be able to filter out Product Y and get an updated weighted average based on the remaining data.
I have the below formula, which works fine when it comes to a straight total( for example, in the chart I provided the reviews come to a total of 20941) however this doesn't work for the data I need when a sumproduct is in play (such as the 88.99)
=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,{0;1;2;3},)),$B$2:$B$5)
Any direction that can be provided would definitely be appreciated.
Product (A) | Reviews (B) | Feedback (C) |
1. W | 1891 | 85.43 |
2. X | 4730 | 88.25 |
3. Y | 6876 | 89.09 |
4. Z | 7444 | 90.27 |
20941 (TOTAL) | 88.99 (Sumproduct) |