assigning numeric value to a string of texts

[TugzMcBoat]

okay this one essentially is a follow up to my last thread but I'll give a brief overview to see if it makes sense

I'm making a spreadsheet that totals the number of hours a person participated in.

different events equate into a different number of hours served

names are listed in column A
Total number of hours are listed in column B
and the events the individual participated in are listed in column C

Column C contains a drop down menu listing the events I'll use states as an example Texas, New York, Washington, California, ect... where multiple selections are possible

the data list for the events is listed in column E,
and the amount of time each corresponding event represents is in column F, i.e.
Texas-1
New York-3
Washington- 4
California- 4

How do I setup the cells in Column B to add up the total that would be listed in the adjacent cell in Column C I.E.

Cell C4 contains: Texas, New York, California
how can I get cell B4 to calculate =1+3+4

thanks

[davegugg]

Could you post a sample workbook with your expected results?

Thank You

[Palmetto]

How do I setup the cells in Column B to add up the total that would be listed in the adjacent cell in Column C I.E.

Cell C4 contains: Texas, New York, California
how can I get cell B4 to calculate =1+3+4

The first order of business is to properly structure your worksheet in an efficient design that facilitates data extraction and analysis. See this link for help on setting a correctly structured worksheet: Excel Best Practice & Design

By letting cell C4 hold several joined text strings, you make the problem of matching and extracting numeric data from column-E much more difficult. Moreover, column-E should only contain text (i.e.,Texas and not Texas-1) and the adjacent column should contain the numbers.

[TugzMcBoat]

The first order of business is to properly structure your worksheet in an efficient design that facilitates data extraction and analysis. See this link for help on setting a correctly structured worksheet: Excel Best Practice & Design

By letting cell C4 hold several joined text strings, you make the problem of matching and extracting numeric data from column-E much more difficult. Moreover, column-E should only contain text (i.e.,Texas and not Texas-1) and the adjacent column should contain the numbers.

Column E does only contain text i just put it as Texas-1 to give a visual. column I would have the numbers

Also I agree that having column C hold multiple strings of text is probably not very efficient. However it is what I have to work with

[TugzMcBoat]

I attached a sample workbook

Column C contains the multiple select drop down from the data range in column G

once selected I need the cells in column B to add the total value of text strings selected (value as indicated in column H)

[NBVC]

That is different then your description....

Try in B2:

=IF(C2="","",VLOOKUP(C2,$G$2:$H$8,2,0))

copied down.

To sum all =SUM(B2:B6)

[JBeaucaire]

In B2 put this array formula:
=IF(C2="","",SUM(ISNUMBER(SEARCH($G$2:$G$8,C2))*($H$2:$H$8)))

...confirmed by pressing CTRL-SHIFT-ENTER. Curly braces { } will appear around the formula to indicate the array is active. Then copy that cell down column B through B6.

As you add multiple choices in column C, the sums will total in B properly.

[NBVC]

How is that any better than a simple Vlookup or am I missing something?

[JBeaucaire]

I hadn't seen your answer nor tested it yet. Now that I have, no, it doesn't work unless he only makes one choice in C2. As soon as he makes a second choice it breaks. That's why I used an array.

[NBVC]

I see.. I hadn't enabled macros.. so didn't realize there were multiple selections in one cell possible...

.. my apologies.

[NBVC]

Another non CTRL+SHIFT+ENTER way would be...

=IF(C2="","",SUM(SUMPRODUCT(--(ISNUMBER(SEARCH($G$2:$G$8,C2))),$H$2:$H$8)))

and we are assuming you can't pick same city twice.. or if you do it will be accounted for once only.