Log In
Register
Home
September 14, 2021
Offline
Good afternoon,
I have followed the steps that were provided in the "Use Excel Slicer Selection in Formulas" video.
I would like to be able to select multiple options within the pivot table though.
Can you please provide steps on how to use the Sumproduct function or another alternative.
Attached is the workbook where the results (starting in cell R3) that is returning the correct details; however, multiple options within the pivot need to be selected at times.
Thank you, again, for all your doing!
See Answer
July 16, 2010
Offline
Hi Jason,
There's no file attached. You have to click the Start Upload button after selecting your file, then wait for the grey check mark beside the file size before submitting your question.
You'd have to allow for all items in the Slicer to be selected, by adding them as OR criteria in the SUMPRODUCT. e.g. if you allowed for 2 items based on my example from the "Use Excel Slicer Selection in Formulas" video, then the formula would look like this:
=SUMPRODUCT(Actual[Actual]*((Actual[Category]=L6)+(Actual[Category]=L5)) * (Actual[Month]>=Report!H5)*(Actual[Month]<=EOMONTH(H5,0)))
Hope that points you in the right direction.
Mynda
September 14, 2021
Offline
I've uploaded my workbook. I'm hoping you would be able to help me with the sumproduct formula based on this data.
July 16, 2010
Offline
Hi Jason,
I see that you have 22 items + some blanks that can be selected in the Slicer. That's 23 OR arguments to manually enter in a SUMPRODUCT formula, which can potentially make the file slow to calculate because Excel has to evaluate every criteria in every instance of the SUMPRODUCT formula in your spreadsheet.
Why don't you just use PivotTable to build the report? That's what they're designed for, i.e. to automate the creation of a load of SUMIFS/SUMPRODUCT formulas.
Mynda
Answers Post
