For years I have been using SUMIFS or GETPIVOTDATA formulas whenever I needed to SUM values from another table or pivot based on some condition. It worked quite well except when I needed to look up SUM for the huge datasets or some complex filter combinations among other things. That is where the third child – CUBEVALUE – was born.
Once CUBEVALUE formula came out about 10 years ago along with PowerPivot, I was delighted because it had filled the gap that was missing in SUMIFS and GETPIVOTDATA formulas for certain usage scenario. Below I write about the common usage of all 3 formulas and their PROs and CONs based on my personal experience. I list various scenarios and then rate them with score 1 to 3, with 1 being lowest and 3 the highest respectively.
USAGE SCENARIOS
Below are details regarding each Usage Scenario:
- Ease of use – here definitely you need to consider the knowledge of Excel vs Pivot table vs Analysis Service/Fabric Semantic model (or Power Pivot) data model. If you are not familiar with Fabric Semantic modeling, then SUMIFS is your best bet, as it is excel only function. GETPIVOTDATA is also excel only function, but you need the knowledge how to correctly setup pivot table in order for the formula to extract correct sums.
- Flexibility – while GETPIVOTDATA can only be strictly configured based on the shape of the pivot table it is referencing to, SUMIFS has a very free-form function syntax, that can sum things in many different ways including passing wildcards (*), greater/smaller or equal “><=” signs. CUBEVALUE has a long form syntax matching the star / snowflake schema of the data model but it has lots of flexibility to sum things based on sets of complex filters (CUBESET function), rankings on the fly (CUBERANKEDMEMBER). Check my other post or video on how to use CUBE functions.
- Speed – when it comes to speed, GETPIVOTDATA is the quickest. If the dataset is small to medium then SUMIFS could also work relatively fast. CUBEVALUE has definitely been a disappointment when it comes to speed. Its very slow, especially when working with OLAP or Fabric Semantic Models. If you have a report with multiple CUBEVALUE functions setup and if you try to refresh a connection, it literally has to recalculate each CUBEVALUE connection for each formula cell, which takes ages. There is a good article from Chris Webb for optimizing that here.
- Small Dataset – for smaller datasets its much quicker and simpler to use SUMIFS. For doing GETPIVOTDATA, you need to first setup a pivot in one of the background tabs of the Excel and then reference formulas to that pivot. For CUBEVALUE, it would become a bit tedious to setup Fabric Semantic Model or PowerPivot backend data, then creating Measures to be referenced by the formula.
- Medium Dataset – using SUMIFS for a 200k rows table could be a bit slow, so its best to use GETPIVOTDATA formulas. You can also use CUBEVALUE, but you need to have a Semantic Model set up in Fabric.
- Huge Dataset – for a big dataset with more than 1 million rows, currently Excel will be very slow and will not handle such data volumes in a normal Table format. Therefore having SUMIFS is almost not possible, as it will kill or freeze Excel instance. If the a huge dataset is loaded in a compact Pivot format, then GETPIVOTDATA can be used. But if you need to get different filters or views in the Pivot, you would need to duplicate Pivots in order to be referenced by GETPIVOTDATA, which will explode Excel file size. Using CUBEVALUE with large Datasets is optimal. Fabric Semantic model resides in Fabric, so your excel file size will be very slim. There is no need to create different pivot tables or views to be referenced for the CUBEVALUE formula.
- Complex formula calculations – if we have a complex formula that SUMs extensive filter combinations (such as customers in Australia, Small Customers, in south Sydney, IT Branch, etc…) then your GETPIVOTDATA will require you to create very detailed background pivots to reference that formula, which is not idea. SUMIFS is better in this case, but the formula writing and readability can get quite complex, especially if you start referencing cells names. The best way is to use CUBEVALUE formula, as it is very readable and can use any extensive filter combination possible.
- Formula Simplicity/Readability – the winner here is CUBEVALUE as without knowing much of the dataset, its easy to right away understand what is being calculated. CUBEVALUE has an advantage over GETPIVOTDATA due to Intellicense while writing the formula. SUMIFS is just not user-friendly at all in this case.
- Backend Setup – SUMIFS doesnt requite any additional backend setups other than a flat table. GETPIVOTDATA requires you to setup multiple background pivot tables that can be referenced by formulas. CUBEVALUE requires to create a Semantic Model in Fabric or in PowerPivot backend which can be complex or time consuming.
- Calculating Ratios On The Fly – here the best choice is CUBEVALUE, as it can easily work with various denominators on the fly to get any ration. SUMIFS is also working ok for this reason, but usability is a bit more complex. GETPIVOTDATA can be used for ratios, but your backend pivot table has to be setup exactly sub-totalling the denominators otherwise it becomes tedious.
- Real-time data – the clear winnner is CUBEVALUE, which uses Fabric’s Direct Lake connection to always be uptodate. Once there is a change to the underlying data, excel recalculates on the fly.
- Security – you can configure Fabric Semantic model to provide user-based access. If you don’t have access to the semantic model, then the formulas will not work when trying to change filters.
CONCLUSION
Finally the winner of the 3 formulas is … its a tough one: SUMIFS – back to the roots! Simplicity is king! Of course, in different scenarios you should be using different formula, but overall SUMIFS has the most usage advantages.
Do you agree or what do you think? Please leave a comment below.
Check my short video on using SUMIFS here formula here: Excel SUMIFS formula tutorial (ganjing.com)
1 thought on “Excel CUBEVALUE vs SUMIFS & GETPIVOTDATA”