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.
Follow us at:
© 2020-2022 ExcelWorld.com