Add numeric value from text cells in horizontal column

[sanjay.k]

Hi,
Please help me to add numeric value from cell (which have text and number) in horizontal way .
I have some data (AL - 1) or (AL - 0.5) from cell "C9" to cell"AG9". some cells are blank and some have "Weekend" mentioned in cells "C9" to "AG9".
I want to add all 1 and 0.5 in cell "AH9"

Thanks in advance for help ! I have attached the sheet for better understanding.

[bluesky63]

Hi sanjay,

may be each of the AL - XX use =IF(LEFT(H9,4 )="AL -", VALUE(RIGHT(H9,LEN(H9)-SEARCH("-",H9))),"") to convert them to number in another row, the sum it AH9

[sanjay.k]

Hi ,
thanks Bluesky63 for your help.
However i cannot do in another row as i have may be 1000 employees

[bluesky63]

Hi Sanjay,

Use array then
=SUM(IF(LEFT(A9:AG9,4 )="AL -", VALUE(RIGHT(A9:AG9,LEN(A9:AG9)-SEARCH("-",A9:AG9))),""))

after entering the formual, CTRL-SHIFT-ENTER

[GeoffW283]

Does your sample data include all possible variants?

If so is the simple formula below enough for your purposes?

It also gives the expected result of 14.

In AH9:

Please Login or Register  to view this content.

[Bo_Ry]

Or try
=SUMPRODUCT(COUNTIFS(C9:AF9,"*"&{1,5})/{1,2})