Ref text in cell in a formula

[jimbob]

I have a spreadsheet of all 500 S&P500 stocks. One col has the sector
each stock is in (i.e. healthcare, financials, consumer etc..) Another
col has the weighting of each stock in the index. I want to add the
total weighting for each stock in each sector. So I would be adding the
weighting for all stocks in the healthcare sector and all stocks in the
industrials and so on. How do I ref the cells with text in a formula to
do this? Thanks again.

 

[daddylonglegs]

Try something like

=SUMIF(A:A,"healthcare",B:B)

where column A contains the text and column B the weightings

 

[Ron Coderre]

Seems like this will work for you:

With Sector in Col_A, Weight in Col_B

=SUMIF(A:A,"Healthcare",B:B)

Does that help?

Regards,
Ron

 

[jimbob]

Thank You!!

That's very close to what I'm looking for but then I would have to type
a separate formula for each sector, right? Isn't there a way to tell
Excel "If the text in A1:A200 is the same, then count how many cells
have that text. And do this for each instance where the text changes
and output the result to another cell". In other words, is there a way
to ref the text without actually having to enter a separate formula for
each instance where the text changes. Hope I'm being clear. My brain
hurts from trying to figure this out

 

[Ron Coderre]

Perhaps a Pivot Table is the better way to go.

Data>Pivot Table
Use Excel
Select your data
Click the [Layout] button

ROW: Drag the Sector field here
DATA: Drag the Weight field here
If it doesn't list as Sum of Weight...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table...and you're done!

That will list each Sector and the Total of the Weights.

To refresh the Pivot Table, just right click it and select Refresh
Data

Is that something you can work with?

Regards,
Ron

 

[jimbob]

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.

 

[Ron Coderre]

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