Sum in Pivot giving "zero" value

[JuliaNorm]

I am pivoting from another workbook via creating a connection, I have TotalPaid field which I need to sum in my Pivot, when I bring the field down to "values" it gives me "count" by default, once I change it to "sum" it gives me "zero", I tried changing field to "number", "currency" I also tried to change field on the back end of workbook via Data-Text to Columns and choose general in last step, but I still get "zero" when I choose "sum" in my pivot.
I did it with two other workbooks, there it worked just fine, I copy/pasted format from workbooks which worked fine to one that is giving me "zero", still nothing, I also try redoing it multiple times from scratch, still same results, I think a few days back after numerous tries I managed to get sum in my pivot, but today I added more date to my source file and I tried to refresh my pivot, not it is giving me "zero" again, so I am back to square one, what am I missing?

[noboffinme]

Hi JuliaNorm,

Can you complete what you want to do without the connection?

I mean use the original data & get it to give you the results you're after?

If you can, then the connection may be the problem, can you step through how you are connecting the data to your worksheet.

[xlnitwit]

when I bring the field down to "values" it gives me "count" by default

Hi,

That quote confirms that your data is coming through as text, which is why the sum evaluates to 0. Could you perhaps provide a sample of the source workbook and explain precisely how you connect to it?

[JuliaNorm]

Step to connecting Data:
1.I'm choosing Connections in Data section
2. Add-Browse for More...
3. Navigating to file location, choosing file, before I click OK I did not change anything in properties, by default excel chooses readwhite mode.
4. Then from existing connections I choose the file and choose "pivot table"

I have two more pivot tables connected to other workbooks the same way, all three pivots are in the same workbook but in different tabs, the other two are fine, in both pivots when the field I need is dragged down to "values" are in "count" and when I change it to sum it calculates sum, only one of them turns to "zero" when switched from "count" to "sum"