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,"")))

 

[Peter_SSs]

Thanks for the XL2BB sample data. Try

=LET(r,'15Apr24'!$J$3:$N$23,COUNTIFS(r,"*AAA*",r,"*JJJ*"))

 

[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.

 

[Peter_SSs]

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

 

[Peter_SSs]

Using your test values of "AAA" and "JJJ" could make up a set of dummy data for J3:N23 showing the sort of variety that might be encountered and post that with XL2BB and tell us what the expected count result should be?

(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[andrebooyzen]

Here is some dummy data for the problem above

 

[andrebooyzen]

BTC Running Sheet.xlsx.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Change dates when creating new week	Ashmore								Gold Coast
2	--->>>	Monday 15Apr24	Tuesday 16Apr24	Wednesday 17Apr24	Thursday 18Apr24	Friday 19Apr24				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				09:00-10:40 Rod S Joslyn B	09:00-10:40 Glenn B Nicolas B	09:00-10:40 Franz H Nicolas B	09:00-10:40 Dom T Joslyn B	09:00-10:20 Rod S Izack M
6	09:30	110			100	100				100	100	100	100	101
7	10:00			10:00-11:40 Mal A Heldy S
8	10:30			100	10:50-12:20 Adam L Joshua L							10:50-12:20 Franz H Ayani D
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				11:00-12:40 Rod S Nicolas B	11:00-12:40 Glenn B Izack M	100	11:00-12:40 Andre B Izack M	11:00-12:40 JJJ AAA
10	11:30	100	100			100				100	100		110	11:00-12:40 JJJ AAA
11	12:00			12:00-13:40 Garry Joshua L								11:00-12:40 JJJ AAA
12	12:30			100								12:30-14:10 Paula W Blake C
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				13:00-15:00 Noel K Ayani D	13:00-14:40 Andre B Emma C	100	13:00-14:40 Paula W Jennifer C	13:00-14:40 Noel K Sophie B
14	13:30		100		100	100				100	100		100	100
15	14:00			14:00-15:40 Paul H Isabella L
16	14:30			100								14:30-16:00 Mike J Sophie B		14:45-16:30 Noel K Joslyn B
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				15:00-16:40 Noel K Ella M		100		110
18	15:30	100	100		100	100				110
19	16:00												16:00-17:20 Janine T Ayani D
20	16:30												100
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
22	17:30		110	110	110
23	18:00
15Apr24

 

[andrebooyzen]

Using your test values of "AAA" and "JJJ" could make up a set of dummy data for J3:N23 showing the sort of variety that might be encountered and post that with XL2BB and tell us what the expected count result should be?

(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

Thanks Peter. See post #4 below.

 

[myall_blues]

You had some mismatched brackets in your formula. Try:

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

but as it stands this formula will only ever return 1, so the COUNT is redundant and this will work equally well:

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

Are you trying to count the occurrences of AAA and JJJ? Is the expected result from your data 3?

 

[andrebooyzen]

You had some mismatched brackets in your formula. Try:

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

but as it stands this formula will only ever return 1, so the COUNT is redundant and this will work equally well:

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

Are you trying to count the occurrences of AAA and JJJ? Is the expected result from your data 3?

Yes 3 is the correct return value. Thanks a lot