If Statement combining cell information

[ladbroke]

Hi, I am trying to think of a way, badly, of combining information together.

As you can see, I have 5 staff members that each work a different shift pattern each week.
I want to see who gets certain double days off in a period of time.
In this example, I have 3 weeks worth of shifts that shows one person getting 2 Monday and Tuesdays off, some getting 1 and even 0.
I'd like my results to go in the cells B16-B20 as they are above.

I believe a combination of if, and, & or may be the best answer (based on a search here) but I can not figure out what to do.

Any help will be much appreciated.

 

[Toadstool]

Hi Ladbroke,

Does this work?

Please note that it does require an empty column after the last data column.

Ladbroke.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
7		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
8		12-Jul-21	13-Jul-21	14-Jul-21	15-Jul-21	16-Jul-21	17-Jul-21	18-Jul-21	19-Jul-21	20-Jul-21	21-Jul-21	22-Jul-21	23-Jul-21	24-Jul-21	25-Jul-21	26-Jul-21	27-Jul-21	28-Jul-21	29-Jul-21	30-Jul-21	31-Jul-21	01-Aug-21
9	Fred	O	O	E	L	E	W	L	O	W	E	H	L	O	O	E	W	Q	L	T	O	O
10	Denis	L	L	O	L	E	W	L	O	W	E	H	L	L	O	E	W	Q	L	T	O	O
11	Sandra	O	O	L	L	E	W	L	O	O	E	H	L	O	O	O	O	Q	L	T	O	O
12	Richard	E	W	O	L	E	W	L	O	O	E	H	L	O	W	E	W	Q	L	T	O	O
13	Daniel	O	O	L	L	E	W	L	O	O	E	H	L	O	O	O	l	Q	L	T	O	O
14
15		Monday & Tuesday Off Combo
16	Fred	1
17	Denis	0
18	Sandra	3
19	Richard	1
20	Daniel	2
Sheet1

Cell Formulas
Range	Formula
B7:V7	B7	=TEXT(B8,"dddd")
C8:V8	C8	=B8+1
A16:A20	A16	=A9
B16:B20	B16	=SUMPRODUCT(($B$7:$V$7="Monday")*($B9:$V9="O")*($C9:$W9="O"))

 

[Crystalyzer]

You can use this user defined function.

Steps:

1. Copy the code below into a VBA module in a copy of your workbook
2. Enter the formula "=MonTueOff($B$8:$V$8,B9:V9)" without the quotes into cell B16
3. Copy it down to cells B17:B20

Function MonTueOff(days As Range, sel As Range) As Long
    Dim d As Variant
    Dim s As Variant
    Dim i As Integer
    Dim c As Long
    
    If sel.Rows.Count <> 1 Then
        MonTueOff = "#ERROR! Select only 1 row of data."
        Exit Function
    End If

    If days.Rows.Count <> 1 Then
        MonTueOff = "#ERROR! Select only 1 row for days of the week."
        Exit Function
    End If

    If days.Columns.Count <> sel.Columns.Count Then
        MonTueOff = "#ERROR! Select the same number of columns for days and data."
        Exit Function
    End If

    d = days.Value
    s = sel.Value
    c = 0
    
    For i = 1 To sel.Columns.Count - 1
        If Weekday(d(1, i), vbMonday) = 1 And s(1, i) = "O" And Weekday(d(1, i + 1), vbMonday) = 2 And s(1, i + 1) = "O" Then
            c = c + 1
        End If
    Next i
    
    MonTueOff = c
End Function

 

[Peter_SSs]

Please note that it does require an empty column after the last data column.

Or you could just shorten each of your ranges by one column since it is impossible to have a 2-day happening starting with the last column of data.

@ladbroke
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Microsoft 365 (& assuming real dates - numbers - in row 8) you could also try this.

21 07 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
7		Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
8		12/07/2021	13/07/2021	14/07/2021	15/07/2021	16/07/2021	17/07/2021	18/07/2021	19/07/2021	20/07/2021	21/07/2021	22/07/2021	23/07/2021	24/07/2021	25/07/2021	26/07/2021	27/07/2021	28/07/2021	29/07/2021	30/07/2021	31/07/2021	1/08/2021
9	Fred	O	O	E	L	E	W	L	O	W	E	H	L	O	O	E	W	Q	L	T	O	O
10	Denis	L	L	O	L	E	W	L	O	W	E	H	L	L	O	E	W	Q	L	T	O	O
11	Sandra	O	O	L	L	E	W	L	O	O	E	H	L	O	O	O	O	Q	L	T	O	O
12	Richard	E	W	O	L	E	W	L	O	O	E	H	L	O	W	E	W	Q	L	T	O	O
13	Daniel	O	O	L	L	E	W	L	O	O	E	H	L	O	O	O	l	Q	L	T	O	O
14
15		Monday & Tuesday Off Combo
16	Fred	1
17	Denis	0
18	Sandra	3
19	Richard	1
20	Daniel	2
Days Off

Cell Formulas
Range	Formula
B16:B20	B16	=COUNT(FILTER(B$8:U$8,B$7:U$7&B9:U9&C9:V9="MondayOO"))

 

[Crystalyzer]

Or you could just shorten each of your ranges by one column since it is impossible to have a 2-day happening starting with the last column of data.

@Peter_SSs - which is exactly what I did in my user defined function GMTA