How to Drilldown Reports in Excel from Sumproduct formula?
Whenever you prepare a report and submit it to your boss, the first and foremost question is where is
the breakup for this figure? How did you arrive it?
You might have arrived at that figure after apllying some filters or doing some analysis with pivot
table. Now you need to repeat the same and provide that breakup.
Some might use pivot table and link the report to it. DataSheet -> Pivot Table -> Report. Now you can
simply trace from your report to pivot and get breakup from pivot. If you have linked at two places in
pivot table, you will be getting two breakups and consolidate the same and provide as single breakup.
Now the problem with the above approach is:
If there is a change in layout of pivot table, your formulas
will go for toss.
If you have forgot to refresh your pivot table, your report is wrong.
If you have linked at multiple cells in pivot table, now you need to get multiple breakups and
consolidate and present as a single breakup.
So, the pivot is good at analysis and not at reporting.
If you can simply get DataSheet -> Report, directly? without using Pivot in middle?
If you can get reports which are not possible using Pivot?
If you can query your DataSheet like SQL using a formula?
If you can get breakup like in pivot table without using pivot table?
SUMPRODUCT is like SQL.
ExcelWorld.com -> Drilldown.Report is like SQL Command.
Please watch the above video to see drill down reports in action.
Only thing you to remember when using sumproduct function is:
Use * for AND condition
Use + for OR condition
Whenever you use OR (+ operator), wrap criteria with SIGN function