sum a range with multiple symbols and assign different values for each question

Hello, I've spent several hours trying to research this question on my own but to no avail. I have a range from c5 to ag5 and I need a formula which would look at each cell in that range and first identify the symbols (if present) in that range and then sum the value for each symbol. The symbols would be something like "8V", which I want to have a value of 8, "7.5V" would equal 7.5 and so forth all the way down to ".5V", etc. thus summing the total vacation hours for that range.
I've tried sumifs, sumproduct, vlookup formulas unsuccessfully. Any assistance would be appreciated.

example:
c5, d5, e5, f5, g5,
8V 4.5V 1V .5V Total from formula should give me 14

hi lilsnoop. so is where data in row 5 or in column C? using your eg in C5:C9, try this array formula:
=SUM(SUBSTITUTE(C5:C9,"v","")+0)

press CTRL + SHIFT + ENTER to confirm

Maybe this regular formula

Please Login or Register  to view this content.

Try this array formula, confirm with Ctrl+Shift+Enter, not just Enter.

Formula:

Please Login or Register  to view this content.

[EDIT]
This CSE array does the same

Formula:

Please Login or Register  to view this content.

Try this variation of benishiryo's array formula

Please Login or Register  to view this content.

Remember to Ctrl+Shift+Enter

Originally Posted by Ace_XL

Try this variation of benishiryo's array formula

Please Login or Register  to view this content.

Remember to Ctrl+Shift+Enter

Your formula is case sensitive, but it might not matter, depends if "V" is a unit e.g. Volts/Vacation, or purely a "symbol".
I suspect the solution would be better not to being case sensitive.

Thanks so much to all of you! Quick question, Marcol I like your formula

Please Login or Register  to view this content.

but I was wondering how I could use it for totaling other symbols without messing up others. Meaning the formula you gave me will calculate the total vacation hours in the range from c5 to ag5. I'd also like to in another cell calculate how much personal leave time used, which is symbolized as "8PL" etc, and I'll have one that will have a symbol "7.5PLv", etc. My question is the formula gives me a #VALUE for symbols with more than one letter. I notice that the "7.5PLv" symbol will confuse my earlier "vacation" total too as the small "v in the PLv" confuses it or so I suspect. I tried the formula you said for the letter casing , but it doesn't work for me. Thanks again for your help!

You are making it hard by having some short codes that are found in your longer codes.

If you are to have single letter codes/flags, then these letters should not occur in any other code/flag.

Best that all codes/flags are unique.

See this worksheet for the formula adapted to handle any length of code/flag.

Normal working time records have multi rows for staffs and multi columns (1-31) for days of month, then Total days.
Each day, staffs will be recorded by, i.e : "8Plv","7PL","LV","V"...or other symbols.
I think we need to sum hours for each staff each symbol in the total columns, each symbol per column, at most right columns
Assuming AH4:AK4 is the four symbols: "Plv","PL","LV","V"
AH5 is the total of "Plv" of 1st staff:
=SUM(IFERROR(--SUBSTITUTE(UPPER($C5:$AG5),UPPER(AH$4),""),0),)
Confirmed with Ctrl-Shift-Enter
Drag across to AK5, then down

@Marcol, I am afraid that there is a mess with sth like "PL" and "PLV"...

You are welcome!

Originally Posted by bebo021999

@Marcol, I am afraid that there is a mess with sth like "PL" and "PLV"...

As I said, short codes should not be contained in any of the longer codes, it just makes the problem unnecessarily complicated.
Best if the codes are all the same length.