One of the most frustrating things when creating a Pivot Table is a value field. The Pivot Table doesn’t automatically show up in Values, and when you drag it there, it shows up as a Count and not a Sum.
The problem is that the field contains alphanumeric values, rather than just numeric values. When Excel isn’t “convinced” that your field contains all numeric, this happens.
Try the T function in your source data to uncover hidden text values T(cell ref). If it’s a numeric value, it will return a blank. If it’s not numeric, it will return the text (including numbers stored as text).