Pivot Table - Field Value is 1


Hi All, 


Can someone point me in the direction to resolve this? I need the value field to match the numbers in the LPH column but it's defaulting to a "1" in the pivot table.  Would a formula that its driven from be affecting it?


image002 (1).png

image001 (8).png

7 Replies


Click anywhere in the pivottable.

Click on the Count of LPH field in the Values area of the PivotTable Fields task pane.

Select Value Field Settings from the drop down menu.

Change the summary function from Count to Sum.

Click OK.

@Hans Vogelaar 


Hi Hans, tried that before but it was giving me this pop up




Select the LPH column.

Set the number format to General or Number.

On the Data tab of the ribbon, click Text to Columns, then click Finish.

Switch to the pivottable and refresh it.

Can you now set the summary function to Sum?

@Hans Vogelaar 

Unfortunately doing the text to columns is affecting the formula I have in the column. I haven't seen that before.

image001 (9).png





@Hans Vogelaar 


Unfortunately that didn't work either. https://1drv.ms/x/s!Agmju254hK9cgkGIukISFqkfapby 


You have to refresh the pivot table after changing the formula: