find range based on 2 cell criteria

[Akira181]

It's been a while since I've last used excel and I'm having a complete mental block today.

I have a calendar tracking multiple people in Column A, and Column B to Column R has the data. The month data is split into two "arrays" of 15 days. So the same person appears twice in one month over two rows if that makes sense.

What I'm trying to do is that when I have selected a name and month, I'd like it to find the two corresponding rows and apply a formula to them (such as count the number of days that say "training" for example)

I tried named the ranges by month, splitting by names and data so I'm only looking for the name in a defined month. So "Jan_Name" for Column A, and "Jan_23" for the data range then using Xlookup twice (one to search top down, the other bottom up)to try and find the corresponding row but I'm not having any joy. Also tried combinations of H/V lookup, index, match with less joy.

Can someone help?

 

[Georgiboy]

Are you able to use XL2BB to share the layout of the sheet?

Remove any sensitive information

 

[Akira181]

Employee schedule rev01.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Jan-23												january
2
3	Names	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
4		1	2	3	4	5	6	7	8	9	10	11	12	13	14
5	Person A		Holiday	training	training	training	training	training	training	Training	Training	Training	Training	Training	Training
6	Person B		Holiday	Holiday	Office	Office	Office			office	office	office	office	office	office
7	Person C		Holiday	office	office	office	office			office	office	office	office	office
8	Person D		Holiday	office	office	office	office			Office	Office	Office	Office	Office
9	Person E		Holiday	Holiday	training	training	training	training	training	training	training	training	training	training	training
10		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
11		15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
12	Person A	training	Training	office	office	holiday	Holiday			Office	Office	Training	Office	Office			Site	Site
13	Person B	Office	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Office	Site
14	Person C		Holiday	office	office	office	office			Office	Office	Office	Holiday	Holiday			Office	Office
15	Person D		office	office	office	office	office			Office	Office	Office	Office	Holiday			Holiday	Office
16	Person E		Training	Training	Training	Training	office			Office	Office	Office	Office	Office		Training	Training	Training
Timetable (2)

Cells with Data Validation
Cell	Allow	Criteria
A5:A9	Any value
A4:P4	Any value
A12:A16	Any value

I was expecting more of a fight to get XL2BB installed on the work laptop but seems to went smoothly. I've posted one month, I have the same layout for each month in the same worksheet. No idea where the data validation part came from though.

So for example, I would like to count the number of days Person A spent in the "Office" for January. I'm trying to use a formula to automate it as much as possible because person A or the month may change.

 

[Georgiboy]

Perhaps the below will help:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		01/01/2023												January
2
3	January	Names	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday					Month	January
4	January		1	2	3	4	5	6	7	8	9	10	11	12	13	14					Name	Person A
5	January	Person A		Holiday	training	training	training	training	training	training	Training	Training	Training	Training	Training	Training					Find What	training
6	January	Person B		Holiday	Holiday	Office	Office	Office			office	office	office	office	office	office					Count	15
7	January	Person C		Holiday	office	office	office	office			office	office	office	office	office
8	January	Person D		Holiday	office	office	office	office			Office	Office	Office	Office	Office
9	January	Person E		Holiday	Holiday	training	training	training	training	training	training	training	training	training	training	training
10	January		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
11	January		15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
12	January	Person A	training	Training	office	office	holiday	Holiday			Office	Office	Training	Office	Office			Site	Site
13	January	Person B	Office	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Office	Site
14	January	Person C		Holiday	office	office	office	office			Office	Office	Office	Holiday	Holiday			Office	Office
15	January	Person D		office	office	office	office	office			Office	Office	Office	Office	Holiday			Holiday	Office
16	January	Person E		Training	Training	Training	Training	office			Office	Office	Office	Office	Office		Training	Training	Training
17
18		01/02/2023												February
19
20	February	Names	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
21	February		1	2	3	4	5	6	7	8	9	10	11	12	13	14
22	February	Person A		Holiday	training	Office	Office	training	training	training	Training	Training	Training	Training	Training	Training
23	February	Person B		Holiday	Holiday	Office	Office	Office			office	office	office	office	office	office
24	February	Person C		Holiday	office	office	office	office			office	office	office	office	office
25	February	Person D		Holiday	office	office	office	office			Office	Office	Office	Office	Office
26	February	Person E		Holiday	Holiday	training	training	training	training	training	training	training	training	training	training	training
27	February		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
28	February		15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
29	February	Person A	training	Training	office	office	holiday	Holiday			Office	Office	Training	Office	Office			Site	Site
30	February	Person B	Office	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Office	Site
31	February	Person C		Holiday	office	office	office	office			Office	Office	Office	Holiday	Holiday			Office	Office
32	February	Person D		office	office	office	office	office			Office	Office	Office	Office	Holiday			Holiday	Office
33	February	Person E		Training	Training	Training	Training	office			Office	Office	Office	Office	Office		Training	Training	Training
Sheet1

Cell Formulas
Range	Formula
V6	V6	=LET( tc,TOCOL(FILTER(C3:S33,(A3:A33=V3)*(B3:B33=V4))), ROWS(FILTER(tc,tc=V5)))
A3:A16	A3	=$N$1
A20:A33	A20	=$N$18

Cells with Data Validation
Cell	Allow	Criteria
V3	List	January,February

 

[Akira181]

that works perfectly but completely not the way I was thinking of doing it (probably why it wasn't working). Never used the LET function before.

I'm assuming tc is a user defined variable? Bit confused as to how it's working

 

[Joe4]

You can find help/explanations/examples of all Excel functions on-line with a simple Google search.
See here: LET function - Microsoft Support

 

[Akira181]

thanks for that. Last question, I'm using a VBA code I found on google to count when certain cells are a different colour (red in this case)

Is there any way to make that work in combination with the LET function Georgieboy wrote? Basically to count the number of red cells per person per month (cell content doesn't matter)

Function CC(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range
Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then
CC = CC + 1
End If

Next Data_Range

End Function

 

[Georgiboy]

You could use a function as below:

Function CountRed(mnthRng As Range, mnthStr As String, personStr As String)
    Dim rCell As Range, pCell As Range, a As Long
    
    For Each rCell In mnthRng.Cells
        If rCell = mnthStr And rCell.Offset(, 1) = personStr Then
            For Each pCell In rCell.Offset(, 2).Resize(, 17).Cells
                If pCell.Interior.Color = vbRed Then
                    a = a + 1
                End If
            Next pCell
        End If
    Next rCell
    
    CountRed = a
End Function

Used in the spreadsheet as below:

PERSON.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1		01/01/2023												January
2
3	January	Names	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday					Month	January
4	January		1	2	3	4	5	6	7	8	9	10	11	12	13	14					Name	Person A
5	January	Person A		Holiday	training	training	training	training	training	training	Training	Training	Training	Training	Training	Training					Find What	training
6	January	Person B		Holiday	Holiday	Office	Office	Office			office	office	office	office	office	office					Count	15
7	January	Person C		Holiday	office	office	office	office			office	office	office	office	office						Red	2
8	January	Person D		Holiday	office	office	office	office			Office	Office	Office	Office	Office
9	January	Person E		Holiday	Holiday	training	training	training	training	training	training	training	training	training	training	training
10	January		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
11	January		15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
12	January	Person A	training	Training	office	office	holiday	Holiday			Office	Office	Training	Office	Office			Site	Site
13	January	Person B	Office	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Office	Site
14	January	Person C		Holiday	office	office	office	office			Office	Office	Office	Holiday	Holiday			Office	Office
15	January	Person D		office	office	office	office	office			Office	Office	Office	Office	Holiday			Holiday	Office
16	January	Person E		Training	Training	Training	Training	office			Office	Office	Office	Office	Office		Training	Training	Training
17
18		01/02/2023												February
19
20	February	Names	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
21	February		1	2	3	4	5	6	7	8	9	10	11	12	13	14
22	February	Person A		Holiday	training	Office	Office	training	training	training	Training	Training	Training	Training	Training	Training
23	February	Person B		Holiday	Holiday	Office	Office	Office			office	office	office	office	office	office
24	February	Person C		Holiday	office	office	office	office			office	office	office	office	office
25	February	Person D		Holiday	office	office	office	office			Office	Office	Office	Office	Office
26	February	Person E		Holiday	Holiday	training	training	training	training	training	training	training	training	training	training	training
27	February		Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday
28	February		15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
29	February	Person A	training	Training	office	office	holiday	Holiday			Office	Office	Training	Office	Office			Site	Site
30	February	Person B	Office	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Site	Office	Site
31	February	Person C		Holiday	office	office	office	office			Office	Office	Office	Holiday	Holiday			Office	Office
32	February	Person D		office	office	office	office	office			Office	Office	Office	Office	Holiday			Holiday	Office
33	February	Person E		Training	Training	Training	Training	office			Office	Office	Office	Office	Office		Training	Training	Training
Sheet3

Cell Formulas
Range	Formula
V6	V6	=IFERROR(LET( tc,TOCOL(FILTER(C3:S33,(A3:A33=V3)*(B3:B33=V4))), ROWS(FILTER(tc,tc=V5))),0)
V7	V7	=CountRed(A3:A33,V3,V4)
A3:A16	A3	=$N$1
A20:A33	A20	=$N$18

 

[Akira181]

that's done the trick. Thanks!

 

[Georgiboy]

You're welcome.

You should probably keep the marked solution as the post that answered the thread title question and not the follow up question. This will help people who search the forum in the future.