Count numbers that are part of text cells with conditions

[danieloverton1984]

Hi,

I hope someone will be able to help me just as they have before.

I want to build in an automatic check to my staff off duty sheet (attached) to make sure that I have 2 or more people arriving at 0800 and 2 or more people staying until 1820.

In B151 I want to count the number of times 0800 appears in the shift times line of each persons rota, but I need to take away from that the number of times "ODU" appears in the top line of each persons shifts.

In B152 I want to count the number of times 1820 appears in the shift times line of each persons rota, but I need to take away from that the number of times ODU appears in the bottom line of each persons shifts.

Then I would copy this across to C151, D, E, F.

I tried starting simple and counting the number of 0800s using the following:
=COUNTIF((B135,B130,B125,B120,B115,B110,B105,B100,B95,B90,B85,B80,B75,B70,B65,B60,B55,B50,B45,B40,B35,B30,B25,B20,B15,B10),"*0800*")
But that returns a #VALUE! result.

I'm not great with excel and would appreciate any help.

[Phuocam]

Try this ...

=SUMPRODUCT((LEFT(B10:B135,4)="0800")*(MOD(ROW(B10:B135),5)=0))

[samba_ravi]

=SUMPRODUCT(ISNUMBER(SEARCH("*0800*",B6:B150))*(MOD(ROW(B6:B150)-MIN(ROW(B6:B150))+1,5)=0))
Try this

[danieloverton1984]

Thanks thats great and counts the total number of 0800 starters. How could I modify that formula to count the number of times ODU appears in ONLY these cells B131,B126,B121,B116,B111,B106,B101,B96,B91,B86,B81,B76,B71,B66,B61,B56,B51,B46,B41,B36,B31,B26,B21,B16,B11,B6.

Thanks.

[Phuocam]

Change (MOD(ROW(B10:B135),5)=0)

to (MOD(ROW(B10:B135),5)=1) etc...

[danieloverton1984]

Try this ...

=SUMPRODUCT((LEFT(B10:B135,4)="0800")*(MOD(ROW(B10:B135),5)=0))

Change (MOD(ROW(B10:B135),5)=0)

to (MOD(ROW(B10:B135),5)=1) etc...

GREAT! Thanks for the help! I dont understand any of it, but it works perfectly. Even managed to modify it to count the 2 groups of staff seperately. Attached is the finished sheet if anyone is interested.

[Phuocam]

You're welcome, good luck!