Thursday, August 1, 2013

Excel GETPIVOTDATA Formula verse SUMIFS Formula

Alright, I am going to expose my immediate bias.  I definitely prefer the GETPIVOTDATA function to the SUMIFS function.

I will discuss the benefits of each formula briefly.  I think that the main benefit of the SUMIFS function is that they are easier to understand and little easier to right.  They are also less likely to return an error result.  For this reason the GETPIVOTDATA function should be nested in an IF function and an ISERROR function.

Example: IF(ISERROR(GETPIVOTDATA(X,X,X,X)),0,ISERROR(GETPIVOTDATA(X,X,X,X))

This basically returns a zero if the GETPIVOTDATA function result is an error or the result if it is not.

This makes writing these formulas a little more difficult.

Now for the reasons I prefer the GETPIVOTDATA function.  The most appealing reason is that it is easier to control the frequency of calculations when working with large tables in a worksheet.  The PivotTable needs to be refreshed before these formulas preced with calculations.  So if you have to modify data  or import new data frequently these don't bog the worksheet down with endless calculation  updates.  You control when the PivotTable is refreshed.  Working from PivotTable's also makes data easier to extract and analyze.  If you spot an issue in a group of data that is being pulled you can simply refer to the PivotTable and double click the number to pull the portion of the table making up your result.

Another advantage is that the GETPIVOTDATA are easier to test and use in templates.  When modifying templates it looks to the PivotTable not to the huge data set.  This makes it easier for these funtions to cacluclate a result.  Now for the secret you don't usually have to do a lot of work to write these  formulas .  Use = then click on a cell from the PivotTable.  Then make it dynamic by changing where the criteria being pulled is a cell referencce.

I think I have made my case.  If I think of any other benefits or contasts I will update this post.