Hello,
Would highly like if you could shed some light on how I can accomplish this.
My sheet has three segments. The first being a selection for the users (the user can choose which category they want to view the reports for):
Selection
# Category Include?
1 Electricals Yes
2 Paper Yes
3 Food Yes
4 Beverages No
5 Pets No
6 Cosmetics No
The second segment has sales records
Sales
Year Month Category Quantity
2010 1 Electricals 5
2010 1 Paper 434
2010 2 Electricals 23
2010 2 Food 13
2010 3 Beverages 2323
And, the third has performance summary / report based on year-month against the selected criterions in the first segment
Summary / Report
Year Month Quantity
2010 1 ???
2010 2 ???
2010 3 ???
The max I've reached for the formula against Quantity is as follows:
=SUMPRODUCT(($C$16:$C$20=D26)*($D$16:$D$20=E26),$F$16:$F$20)
but, this doesnt consider the selection that the user has made, and as such the month-wise totals are coming up. I'm aware that I can use PivotTable with ease for this requirement, but I'd ideally prefer a non-PivotTable solution as there are many other dependencies.
Also, if the user-selected categories can be dynamically captured as a named range - could use it for several validation moving forward.
Thanks in advance!
Noaman
Bookmarks