Forum Discussion
Solved
Sum 2 pivot table Fields where one may not exist
I have to sum in a pivot with sales at Status 1-5 the 4's and 5's . However , in some peoples pivot maybe they don't have a value in both status's and in that case the sum returns 0 I cannot ...
You could construct a formula along the following lines:
=IFERROR(GETPIVOTDATA("Value",$F$3,"Status",4),0)+IFERROR(GETPIVOTDATA("Value",$F$3,"Status",5),0)It sums data from the pivot table for the Value field where the Status is 4 and 5. If either of these statuses does not exist, the IFERROR returns a zero, rather than a #REF! error.
The attached file has a small example demonstrating this.
You could construct a formula along the following lines:
=IFERROR(GETPIVOTDATA("Value",$F$3,"Status",4),0)+IFERROR(GETPIVOTDATA("Value",$F$3,"Status",5),0)It sums data from the pivot table for the Value field where the Status is 4 and 5. If either of these statuses does not exist, the IFERROR returns a zero, rather than a #REF! error.
The attached file has a small example demonstrating this.
Thanks , worked perfectly