Assistance with CountIF from a list

[AceForSale]

Currently I am using a bunch of OR(CountIF statements to search a column for words that begin with something. Ex:

IF(OR(COUNTIF($Q2,"abc-"&"*"),COUNTIF($Q2,"def-"&"*"),COUNTIF($Q2,"ghi"&"*"),COUNTIF($Q2,"jkl-"&"*"),COUNTIF($Q2,"mno-"&"*"),COUNTIF($Q2,"pqr-"&"*"),COUNTIF($Q2,"stv-"&"*"),COUNTIF($Q2,"wzy-"&"*")),"Exempt","")

Is there a way to use one CountIF but have it check through a list? so this way its less coding and in the event I have to another another one I can just add it to the list

List Ex:
abc-
def-
ghi-
jkl-
mno-
qrs-
tvu-
wzy-

The hard part of this is the letters start each word so I need to have it search the table for anything beginning with and can have a wild card after.
Thank you

[Phuocam]

List Ex: -> range A1:A10

Try this ...

=IF(MAX(COUNTIF($Q2,$A$1:$A$10&"*")),"Exempt","")

[Phuocam]

Or ...

=IF(OR(COUNTIF($Q2,{"abc-*","def-*","ghi*","jkl-*","mno-*","pqr-*","stv-*","wzy-*"})),"Exempt","")

[AceForSale]

Unfortunately it didn't work.
Here is my current code.

=IF($G2="","",
IF(MAX(COUNTIF($Q2,HelpDeskExempt!$A$1:$A$25 & "*")),"Exempt",
IF(MAX(COUNTIF($K2,HelpDeskSubNets!$A$1:$A$50 & "*")),"Help Desk",
"Other"))))

Everything shows up as Other so the Except and Help Desk lines aren't working.
Any suggestions?

[JohnTopley]

Try

=SUM(COUNTIF(Q2,A1:A25 & "*"))

Enter with Ctrl+Shift+Enter

will return 1 if Q2 found in A1:A25

[Jonmo1]

You can change SUM to SUMPRODUCT so it doesn't require CTRL + SHIFT + ENTER

And it's important that there are no blanks in the list (A1:A8)

=IF(SUMPRODUCT(COUNTIF($Q2,$A$1:$A$8&"*")),"Exempt","")

[AceForSale]

Testing...

[AceForSale]

Ok Jon and John it worked, I did the SumProduct so I wouldn't have to make it an array. But its giving false results. Its saying except for items that are not on the list. I'm looking over it now to see if I can figure out what its doing wrong

[Jonmo1]

As I said in my post.

It's IMPORTANT that there are NO BLANKS in the range with your list A1:A8

[AceForSale]

Jonmo1 my apologies I missed that, reviewing now.

[AceForSale]

Jonmo1, you were correct. I had a space at the end. By adjusting the range it corrected it. Thank you all very much for your assistance as it is now working.
Jon, out of curiosity, why does that extra space throw it off?

[Jonmo1]

If one of the cells in A1:A8 is blank, then the countif for that blank cell translates to

=COUNTIF($Q2,""&"*")
=COUNTIF($Q2,"*")

With that being a wildcard, it will consider any text value of Q2 as a match to "*"

[AceForSale]

Makes complete sense, Thank you!

[Jonmo1]

You're welcome.