Searching for 2 different values in the same cell in a different tab

[andrebooyzen]

Hi All

I need some help please. I am using the following formula to search and count the occurrences of 2 different values occurring together in the same cell in a separate tab. I am searching a range of cells in the tab and the matches may occur more than once in the range. In this case I am just testing the formula by using AAA and JJJ to search for. Excel keeps saying there is an error in the formula. Any help would be appreciated. Thanks

=COUNT(IF(OR(ISNUMBER(SEARCH("AAA",'15Apr24'!$J$3:$N$23)),ISNUMBER(SEARCH("JJJ",'15Apr24'!$J$3:$N$23)),1,"")))

 

[andrebooyzen]

If you look in the XLSB sheet I have just pasted, you will see an example. I just used the AAA and JJJ as a test.
The first search always appears first in the cell and the 2nd one always second (i.e. AAA is always first and JJJ second). They will never be swapped around.
In the sheet attached cells C21 and D21 contain the same 2 people. Also cells E21 and F21.
What I am trying to do is count the number of times the first name is matched with the second name in a matrix.

BTC Running Sheet.xlsx.xlsb
	A	B	C	D	E	F
1	Change dates when creating new week	Ashmore
2	--->>>	Monday 15Apr24	Tuesday 16Apr24	Wednesday 17Apr24	Thursday 18Apr24	Friday 19Apr24
3	08:00			08:00-09:40 Andre B Ebony B
4	08:30		08:30-10:10 Andre B Mika Y	110
5	09:00	09:00-10:40 Colin B Mika Y	110		09:00-10:40 Adam L Jordyn L	09:00-11:20 Martin A Mika Y
6	09:30	110			100	100
7	10:00			10:00-11:40 Mal A Heldy S
8	10:30			100	10:50-12:20 Adam L Joshua L
9	11:00	11:00-12:40 Colin B Heldy S	11:00-12:40 Paul H Jordyn L		100	11:00-12:40 Ashley C Alexander B
10	11:30	100	100			100
11	12:00			12:00-13:40 Garry Joshua L
12	12:30			100
13	13:00		13:00-14:20 Alex V Pamela A		13:00-14:40 Rod M Isabella L	13:00-14:40 Rod M Pamela A
14	13:30		100		100	100
15	14:00			14:00-15:40 Paul H Isabella L
16	14:30			100
17	15:00	15:00-16:40 Mal A Victoria D	15:00-16:40 Pam M Ebony B		15:00-16:40 Garry Ebony B	15:00-16:40 Steve M Saleha I
18	15:30	100	100		100	100
19	16:00
20	16:30
21	17:00		17:00-18:40 Jan N Farishta J	17:00-18:40 Jan N Farishta J	17:00-18:40 Callen P Mia A	17:00-18:40 Callen P Mia A
22	17:30		110	110	110	111
23	18:00
15Apr24

 

[andrebooyzen]

This is the matrix I am trying to populate....
Thanks Peter

BTC Running Sheet.xlsx.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
110	M/P	Alexander B	Brenden H	Ebony B	Farishta J	Heldy S	Isabella L	Jordyn L	Joshua L	Melessa S	Mia A	Mika Y	Pamela A	Saleha I	Victoria D	Zoe M	NO P
111	Adam L
112	Alex V
113	Andre B
114	Ashley C
115	Bruce S
116	Callen P
117	Chris R
118	Colin B
119	Garry
120	Helen B
121	Jan N
122	Mal A
123	Martin A
124	Noel K
125	Pam M
126	Paul H
127	Rod M
128	Rod S
129	Steve M
130	NO M
15Apr24

 

[Peter_SSs]

The first search always appears first in the cell and the 2nd one always second (i.e. AAA is always first and JJJ second). They will never be swapped around.

In that case try this. Remove all the formulas from B11:Q130 and place the formula shown below in B111. No need to copy it anywhere.

24 04 10_1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Change dates when creating new week	Ashmore
2	--->>>	Monday 15Apr24	Tuesday 16Apr24	Wednesday 17Apr24	Thursday 18Apr24	Friday 19Apr24
3	08:00			08:00-09:40 Andre B Ebony B
4	08:30		08:30-10:10 Andre B Mika Y	110
5	09:00	09:00-10:40 Colin B Mika Y	110		09:00-10:40 Adam L Jordyn L	09:00-11:20 Martin A Mika Y
6	09:30	110			100	100
7	10:00			10:00-11:40 Mal A Heldy S
8	10:30			100	10:50-12:20 Adam L Joshua L
9	11:00	11:00-12:40 Colin B Heldy S	11:00-12:40 Paul H Jordyn L		100	11:00-12:40 Ashley C Alexander B
10	11:30	100	100			100
11	12:00			12:00-13:40 Garry Joshua L
12	12:30			100
13	13:00		13:00-14:20 Alex V Pamela A		13:00-14:40 Rod M Isabella L	13:00-14:40 Rod M Pamela A
14	13:30		100		100	100
15	14:00			14:00-15:40 Paul H Isabella L
16	14:30			100
17	15:00	15:00-16:40 Mal A Victoria D	15:00-16:40 Pam M Ebony B		15:00-16:40 Garry Ebony B	15:00-16:40 Steve M Saleha I
18	15:30	100	100		100	100
19	16:00
20	16:30
21	17:00		17:00-18:40 Jan N Farishta J	17:00-18:40 Jan N Farishta J	17:00-18:40 Callen P Mia A	17:00-18:40 Callen P Mia A
22	17:30		110	110	110	111
23	18:00
24
25
108
109
110	M/P	Alexander B	Brenden H	Ebony B	Farishta J	Heldy S	Isabella L	Jordyn L	Joshua L	Melessa S	Mia A	Mika Y	Pamela A	Saleha I	Victoria D	Zoe M	NO P
111	Adam L	0	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0
112	Alex V	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
113	Andre B	0	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0
114	Ashley C	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
115	Bruce S	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
116	Callen P	0	0	0	0	0	0	0	0	0	2	0	0	0	0	0	0
117	Chris R	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
118	Colin B	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0
119	Garry	0	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0
120	Helen B	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
121	Jan N	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0
122	Mal A	0	0	0	0	1	0	0	0	0	0	0	0	0	1	0	0
123	Martin A	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
124	Noel K	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
125	Pam M	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
126	Paul H	0	0	0	0	0	1	1	0	0	0	0	0	0	0	0	0
127	Rod M	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0
128	Rod S	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
129	Steve M	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
130	NO M	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
15Apr24

Cell Formulas
Range	Formula
B111:Q130	B111	=COUNTIF(B3:F23,"*" &A111:A130&"*"&B110:Q110&"*")
Dynamic array formulas.

 

[andrebooyzen]

Amazing. That worked like a charm.
Thanks so much Peter. Really appreciate it.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.