Finding multiple criteria in nth rows

I've spent way too long thinking about this so would really appreciate some bigger brains on this.

I've been given a spreadsheet with varying shift patterns. I need to find out how many people are on any given shift. Unfortunately, I can't change the fact the times have been put in rows. Start time is in row 4 for Person A, finish time in row 5. Person B is start in row 8 and finish row 9, etc.

This is where I've got to:

{=SUMPRODUCT((MOD(ROW(B4:B200)-ROW(B4),4)=0)+0,ISNUMBER(SEARCH(D1,B4:B200))+0)}

Column A is the person's name. Column B is the times. Column D is a list of times to match; so for example D1 would be 06:00.

The above works correctly in looking at every 4th row for the time D1(06:00) and returning the correct number of instances between B4:B200.

What I need is to replace "D1" with, ideally, greater than and less than so I can be more precise. Or a way to count every possible combination looking at start times (every 4th row) and finish times (every 5th row).

TIA

OK - is very boring though.

How about

Formula:

Please Login or Register  to view this content.

Thanks for considering this Fluff13.

I love your formula but....I have to count the shifts which start and finish at random times. So, the solution would show a list:

1 January 2 January 3 January
06:00 to 14:00 x shifts covered x shifts covered x shifts covered
07:00 to 13:00 x shifts covered x shifts covered x shifts covered
07:30 to 22:00 x shifts covered x shifts covered x shifts covered
etc)

I can use as many rows under the columns but cannot add columns. I assume the logic would go: SUMPRODUCT( if each (4th row >06:00 and <10:00) AND if each (5th row >10:00 and <15:00) = total 'early shifts'.

The same formula could be used to find 'late shifts' say between 14:00 and 16:00 on every 4th row and between 20:00 and 22:00 on every 5th row. 'All day shifts' would be finding all those between 06:00 and 10:00 on every 4th row and between 20:00 and 22:00 on every 5th row!

5th and 6th rows contain other info so using ISEVEN and ISODD returns that cell info.

Thanks!

OK - have had difficulty anonymising the attached so hope this makes more sense now. Highlighted in green on the tabs "JAN" and "Data tables" are the areas I'm trying to work out. Any other suggestions gladly accepted - but the whole thing is much larger and some bits I can't change. Thanks ever so.

How about

Formula:

Please Login or Register  to view this content.

By jove I think you have got it. Thanks very much Fluff13 - excellent formula. I will work with it for a while and see what happens............I may be back. Thank you very much for your time.

Glad to help & thanks for the feedback

Small, tiny extra favour please.... formula works ace for finding start (or finish) times and I can adjust to find any times from the 'lookups' list. From those calculations I can get most of the info but can't find a way to adjust to count how many shifts would stretch the whole day. I'm assuming that would be impossible as it would have to look at the start and finish row for each person using the criteria <10:00 for start and >20:00 for finish?

I'm afraid I can't think of a way of doing that.

I know hurts my head even thinking about it. Nevermind, thanks for the giant leap forward you have given me. Most likely would need some VBA to sort it out. Ta again.