Find a letter in a colum

**excel100** · 02-21-2012, 12:43 PM

Dear All,
I have a table of employees. I want to create a formula to look for holidays if found take number and put it on monthly holiday taken as example.

1 2 3 4 5 6 ....... 31 32 33
A Jun days 1 2 3 4 5 6 7 8 9 10 .... Holiday Sickness Worked
B John H7.5 H3 H3.5
C
:
:
John has taken holiday on 1st Jun 7.5 Hours on 5th Jun 3 hours on 9th Jun 3.5 hours.
I want to create a formula to look for the cell if there is H letter take the number and Sum all holidays.

the following formula is working fine
=SUM(IF(MID(B2,FIND("H",B210)+1,5) > 0, MID(B2,FIND("H",B2)+1,5), 0), IF(MID(B6,FIND("H",B6)+1,5) > 0, MID(B6,FIND("H",B6)+1,5), 0))

But If I use cell B2,3,4, It doesn't work because it there is no H letter in B2,3,4.

Can you please tell me how to fix the problem if I add all days of month in the formula if there is H pick the number if there is no H ignore the cell?

Please let me know if you need more details.
Thanks in advance

Excel100

**NBVC** · 02-21-2012, 12:48 PM

Try:

=SUMPRODUCT(--(LEFT(A1:C1)="H"),--SUBSTITUTE(A1:C1,"H",""))

ajusting A1:C1 range to suit.

This sums all cells in the range that have an "H" prefix.

**excel100** · 02-21-2012, 07:57 PM

Thanks for reply. When I put the formula I get #VALUE!.

**daddylonglegs** · 02-21-2012, 08:05 PM

Try this version

=SUMPRODUCT(--(LEFT(A1:C1)="H"),--(0&SUBSTITUTE(A1:C1,"H","")))

**excel100** · 02-24-2012, 09:07 AM

Dear daddylonglegs,

If I have only H or number it works ok. If I put S in a cell with number I get VALUE.

Thanks for replies.

Excel100

**NBVC** · 02-24-2012, 09:18 AM

I just tested with a combination of S and numbers and H and numbers, and I did just S and numbers, and it gave me a numeric result.

Can you give example of what you have that causes the error?

**excel100** · 02-24-2012, 09:45 AM

As example

May----1-----2-----3-----4-----5-----6--- Total Holiday---Total Sickness---Total worked

John--H2.5---7.5---S7.5--7.5---7.5--H7.5-----10-------------7.5---------------15

On May John has taken 10 hours holiday 7.5 hours sickness and 15 hours work.

Hope I have explained clear. All total columns will have the same formula but finding the specific letter (H or S).

Thanks
Excel100

**NBVC** · 02-24-2012, 09:48 AM

Try:

=SUMPRODUCT(--(LEFT(A1:C1)="H"),--(0&SUBSTITUTE(SUBSTITUTE(A1:C1,"H",""),"S","")))

**excel100** · 02-24-2012, 10:01 AM

Thanks a lot it worked fine. But if I add more Unpaid hours like UP I get VALUE. It looks like it doesn't work with two letters.

=SUMPRODUCT(--(LEFT(A10:F10)="UP"),--(0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A10:F10,"UP",""),"H",""),"S","")))

Thanks
Excel