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]

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

 

[Peter_SSs]

Thanks for the XL2BB sample data. Try

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

 

[andrebooyzen]

Thanks for the XL2BB sample data. Try

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

That worked 100%. Thank you so much Peter. Much appreciated.
What would I have to change if AAA and JJJ were to be values picked up from another cell? e.g. A111

 

[Peter_SSs]

if AAA and JJJ were to be values picked up from another cell? e.g. A111

1. How would the two values appear in A111?
2. Are the two values always in the same order in the calendar? That is, in your previous example, all 3 cells had JJJ listed before AAA. Would that always be the case or might some cells have JJJ before AAA and some cells have AAA before JJJ?