Counting Holiday & Sunday as leaves

[misrasomendra]

Hi all excel experts,

Here I have a problem with counting number of leaves. Suppose below is my data
If you see I am counting total leaves for each employee. But here is one condition if between two leaves any sunday or any holiday will come that should also be counted as leaves.Like Name 2 there is one Holiday (H) and one Sunday(S) followed by and precedded by L. So they should also get counted as Leaves. There can be situations like PPLLLHHHLLLPP or PPPPLLSLPPH.

Emp Name	1	2	3	4	5	6	7	8	TOTAL LEAVE
Name 1	P	P	L	L	H	S	P	L	3
Name 2	P	L	L	L	H	S	L	P	6
Name 3	P	P	P	P	H	S	P	P	0

<TBODY>
</TBODY>

Any help regarding what formula should deliver me the result. I think frequecy can do it but I am unable to figure out how?

Thanks in advance.

Regards!
SM.

 

[markmzz]

Hi Markmzz,

Thanks again. Well VBA is another option I can use but I want a formula solution for this one. Lets wait for other experts to come up with their solution.

Regards.

Try this formula (without helper column):

In AG2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(LEN(AH2)-LEN(SUBSTITUTE(
SUBSTITUTE(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2,"S","H"),
"L"&REPT("H",ROW($1:$29))&"L","LL",TRANSPOSE(ROW($1:$29)))))+COUNTIF(B2:AF2,"L")

And tell me if works.


Markmzz

 

[markmzz]

A small modification in my last formula (now without helper column):

In AG2 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(LEN(SUBSTITUTE(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2,"S","H"))-
LEN(SUBSTITUTE(SUBSTITUTE(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2&AE2&AF2,"S","H"),
"L"&REPT("H",ROW($1:$29))&"L","LL",TRANSPOSE(ROW($1:$29)))))+COUNTIF(B2:AF2,"L")

Markmzz