I am working with a pivot table that represents customer satisfaction. The answer options are 1-5 and DK. The table is formatted with the questions as rows and the responses as the columns. If all columns are present this GPD formula works fine: IF(GETPIVOTDATA("Unique Identifier",$A$3,"Attribute","Overall, how satisfied are you with the Army's Warrior Transition Unit Program?","Value","3. NEITHER SATISFIED NOR DISSATISFIED")+GETPIVOTDATA("Unique Identifier",$A$3,"Attribute","Overall, how satisfied are you with the Army's Warrior Transition Unit Program?","Value","DK/ REFUSE to ANSWER")>0,GETPIVOTDATA("Unique Identifier",$A$3,"Attribute","Overall, how satisfied are you with the Army's Warrior Transition Unit Program?","Value","3. NEITHER SATISFIED NOR DISSATISFIED")+GETPIVOTDATA("Unique Identifier",$A$3,"Attribute","Overall, how satisfied are you with the Army's Warrior Transition Unit Program?","Value","DK/ REFUSE to ANSWER"),0) If there are no responses for DK/ Refuse to Answer the formula returns #ref!. Any ideas on how to get this to work correctly?
Hi Roland,
The GETPIVOTDATA function can only return results if the data is present in the PivotTable, which is why it returns an error when you have no responses for a field.
You can try going into the row or column label 'Field Settings' (right-click the field in the PivotTable) and on the 'Layout & Print' tab of the dialog box choose 'Show items with no values'.
Mynda
Thank you for the response. that option is grayed out for columns and rows
Hi Roland,
I worked for me, see file attached. Be sure to select a cell in the column labels area before right clicking > Field Settings (not 'Value Field Settings') > Layout & Print tab > 'Show items with no data'.
Mynda
You can use IFERROR to return 0 for such cases. You can also use an array of criteria and SUMPRODUCT to test multiple options in the same field - for example:
=SUMPRODUCT(IFERROR(GETPIVOTDATA("Unique Identifier",$A$4,"Attribute","Overall, how satisfied are you with the program","Value",{"4. SOMEWHAT SATISFIED","5. COMPLETELY SATISFIED"}),0))
is equivalent to:
=IF(GETPIVOTDATA("Unique Identifier",$A$4,"Attribute","Overall, how satisfied are you with the program","Value","4. SOMEWHAT SATISFIED")+GETPIVOTDATA("Unique Identifier",$A$4,"Attribute","Overall, how satisfied are you with the program","Value","5. COMPLETELY SATISFIED")>0,GETPIVOTDATA("Unique Identifier",$A$4,"Attribute","Overall, how satisfied are you with the program","Value","5. COMPLETELY SATISFIED")+GETPIVOTDATA("Unique Identifier",$A$4,"Attribute","Overall, how satisfied are you with the program","Value","4. SOMEWHAT SATISFIED"),0)
but also handles missing data.
Thank you Mynda and Velouria, both solutions work. I will be using Velouria's suggested formula.
Nice formula, Velouria!
Thanks. 🙂
