Drilldown formulas like in Pivot Table

• 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.
• What if:
  • 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?
• Aggregate functions SUMIF(S),COUNTIF(S) and SUMPRODUCT are like SQL WHERE CLASUE
• ExcelWorld.com -> Drilldown.Report is like SQL Command provides Drilldown report like in Pivot table
• When to use SUMIFS and when to use SUMPRODUCT?
  • Always use SUMIFS/COUNTIFS as it is fast and supports wildcard
  • Use SUMPRODUCT in cases where you need support for OR clause. SUMIFS support for OR clause is limited to single column
• Only thing you need 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
• Please watch below video to see drill down reports in action.
• Youtube video link Drilldown Reports