The good news is that all we have to build off the previous formula: If we want to pull the sales that Rep A had for Product A in Store A, that means we now have three criteria instead of just one. Let’s make this calculation a bit more complex and add both a product field and one for the sales rep as well: Using multiple criteria in the GETPIVOTDATA function This would give me a value of 394,380 – which is the total of everything in the pivot table. If I only used the first two arguments of the GETPIVOTDATA function, it would return the total for the entire pivot table: Think of it similarly to how you might use a SUMIFS function where you first specify what you want to sum, and where you can keep adding criteria to it. In essence, the GETPIVOTDATA starts with selecting the field you want to pull data from, the second argument pointing to somewhere on the pivot table, and with the third and fourth arguments relating to the relevant criteria. “Store” is the third argument, and it’s the field that relates where the “Store A” item is found, which is the last argument of the formula. However, if the range refers to a cell that isn’t on the pivot table, you’ll get a #REF error. For example, I could use B5 or B6 as long as the cell is located on the pivot table, the result will remain the same. However, this specific cell doesn’t matter, as long as the range is somewhere on your pivot table. “Total Sales” is the first argument, and it is the name of the data field that I’m pulling my numbers from. Let’s break down the different parts of this formula:
=GETPIVOTDATA(“Total Sales”,$B$4,”Store”,”Store A”) If I select the cell that has the sales for Store A, Excel populates the following formula:
0 kommentar(er)
