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

andrebooyzen

New Member
Joined
May 30, 2023
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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,"")))
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.)
 
Upvote 0
Here is some dummy data for the problem above

1712715353298.png
 
Upvote 0
BTC Running Sheet.xlsx.xlsb
ABCDEFGHIJKLMN
1Change dates when creating new weekAshmoreGold Coast
2--->>>Monday 15Apr24Tuesday 16Apr24Wednesday 17Apr24Thursday 18Apr24Friday 19Apr24Monday 15Apr24Tuesday 16Apr24Wednesday 17Apr24Thursday 18Apr24Friday 19Apr24
308:0008:00-09:40 Andre B Ebony B
408:3008:30-10:10 Andre B Mika Y110
509:0009:00-10:40 Colin B Mika Y11009:00-10:40 Adam L Jordyn L09:00-11:20 Martin A Mika Y09:00-10:40 Rod S Joslyn B09:00-10:40 Glenn B Nicolas B09:00-10:40 Franz H Nicolas B09:00-10:40 Dom T Joslyn B09:00-10:20 Rod S Izack M
609:30110100100100100100100101
710:0010:00-11:40 Mal A Heldy S
810:3010010:50-12:20 Adam L Joshua L10:50-12:20 Franz H Ayani D
911:0011:00-12:40 Colin B Heldy S11:00-12:40 Paul H Jordyn L10011:00-12:40 Ashley C Alexander B11:00-12:40 Rod S Nicolas B11:00-12:40 Glenn B Izack M10011:00-12:40 Andre B Izack M11:00-12:40 JJJ AAA
1011:3010010010010010011011:00-12:40 JJJ AAA
1112:0012:00-13:40 Garry Joshua L11:00-12:40 JJJ AAA
1212:3010012:30-14:10 Paula W Blake C
1313:0013:00-14:20 Alex V Pamela A13:00-14:40 Rod M Isabella L13:00-14:40 Rod M Pamela A13:00-15:00 Noel K Ayani D13:00-14:40 Andre B Emma C10013:00-14:40 Paula W Jennifer C13:00-14:40 Noel K Sophie B
1413:30100100100100100100100
1514:0014:00-15:40 Paul H Isabella L
1614:3010014:30-16:00 Mike J Sophie B14:45-16:30 Noel K Joslyn B
1715:0015:00-16:40 Mal A Victoria D15:00-16:40 Pam M Ebony B15:00-16:40 Garry Ebony B15:00-16:40 Steve M Saleha I15:00-16:40 Noel K Ella M100110
1815:30100100100100110
1916:0016:00-17:20 Janine T Ayani D
2016:30100
2117:0017:00-18:40 Jan N Farishta J17:00-18:40 Jan N Farishta J17:00-18:40 Callen P Mia A
2217:30110110110
2318:00
15Apr24
 
Upvote 0
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.
 
Upvote 0
You had some mismatched brackets in your formula. Try:
Excel Formula:
=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:
Excel Formula:
=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?
 
Upvote 0
You had some mismatched brackets in your formula. Try:
Excel Formula:
=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:
Excel Formula:
=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 :)
 
Upvote 0
Thanks for the XL2BB sample data. Try
Excel Formula:
=LET(r,'15Apr24'!$J$3:$N$23,COUNTIFS(r,"*AAA*",r,"*JJJ*"))
 
Upvote 1
Thanks for the XL2BB sample data. Try
Excel Formula:
=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
 
Last edited:
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top