# If Statement combining cell information

##### Board Regular
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.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

##### Well-known Member

Does this work?

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

Cell Formulas
RangeFormula
B7:V7B7=TEXT(B8,"dddd")
C8:V8C8=B8+1
A16:A20A16=A9
B16:B20B16=SUMPRODUCT((\$B\$7:\$V\$7="Monday")*(\$B9:\$V9="O")*(\$C9:\$W9="O"))

#### Crystalyzer

##### Well-known Member
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
VBA Code:
``````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

##### MrExcel MVP, Moderator
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.

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
ABCDEFGHIJKLMNOPQRSTUV
7MondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySunday
812/07/202113/07/202114/07/202115/07/202116/07/202117/07/202118/07/202119/07/202120/07/202121/07/202122/07/202123/07/202124/07/202125/07/202126/07/202127/07/202128/07/202129/07/202130/07/202131/07/20211/08/2021
9FredOOELEWLOWEHLOOEWQLTOO
10DenisLLOLEWLOWEHLLOEWQLTOO
11SandraOOLLEWLOOEHLOOOOQLTOO
12RichardEWOLEWLOOEHLOWEWQLTOO
13DanielOOLLEWLOOEHLOOOlQLTOO
14
15Monday & Tuesday Off Combo
16Fred1
17Denis0
18Sandra3
19Richard1
20Daniel2
Days Off
Cell Formulas
RangeFormula
B16:B20B16=COUNT(FILTER(B\$8:U\$8,B\$7:U\$7&B9:U9&C9:V9="MondayOO"))

#### Crystalyzer

##### Well-known Member
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

Replies
0
Views
590
Replies
0
Views
88
Replies
2
Views
184
Replies
1
Views
200
Replies
0
Views
215

1,148,190
Messages
5,745,246
Members
423,938
Latest member
manilaphilips

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back