Ref text in cell in a formula

When I do as you said it gives me a table with sectors in "Row" and "Total" equals the number of stocks in each sector, not the total weighting. The figures I have in the Weighting Col are the percentage that each stock makes up of the total, so the Grand Total should be 100. In this case the Grand Toatal is the sum of stocks in each sector (499).

The Weighting Col has numbers like .0245%, .0059%, .0061% and so on. So lets say those three numbers were the Weighting for all 3 stocks in a sector (Banks for example). Then the "Total" field for Banks should be the sum of those 3 figures. Instead the Total field is simply the number of stocks in each sector. Thanks again.

I'm a bit puzzled.

Let's say you have 3 columns:
Sector
Stock
Weighting

Sector: Healthcare, Manufacturing, etc
Stock: A, B, C, etc
Weighting: various percents 0.025%, 0.135%, etc
(the whole Weighting coumn totals to 100%)

In the Pivot Table:
Sector is the ROW
Weighting is the DATA and is set to Sum of Weighting

The Total Column can only be the sum of percents

Note: if you put the Stock field into the DATA area, Excel can only use the count because math doesn't work with text fields.

I think the only way you'd be stuck with a count of the Weighting percents is if they are really text fields masquerading as numbers.

Example:
0.0123% is a number
'0.0123% is a word

Try this:
Select the Weighting cells
Format|Cells|Number tab
Category: Percentage
Click the [OK] button

While those cells are still selected:
Data|Text-to-Columns
Click the [Finish] button
(Those steps should ensure that the Weighting values are truly numeric)

Refresh the pivot table.

Any satisfaction?

Regards,
Ron