Conditional Formatting - Multiple wildcard criteria

[robtyketto]

Greetings,

I have a column with values and I change the fill using a set of conditional formatting rules with the criteria below (well as much as I can remember from work, so these are not the exact values but the same principle)

=COUNTIF(A1:A100, "*AA*")
=COUNTIF(A1:A100, "*AB*")
=COUNTIF(A1:A100, "*AC*")

This works fine, however instead of having xx rules I was trying to see if a single function would search a column for a number of wildcard values and highlight them.

I cant use arrays, so they are out of the picture.
I thought using COUNTIFS to specificy multiple criteria e.g. =COUNTIFS(A1:A100,"*AA*",A1:A100,"*AB*") this didn't work.

Can I have one conditional formatting rule to trigger where text is like "*AA*", "*AB*", "*AC*" etc..,?

Not posted here in ages, brings back memories when I used to post more frequently when first started to use excel in the mid-late 90's!

 

[shinigamilight]

This should do the job.

=OR(COUNTIFS(A1:A1,"*AB*"),COUNTIFS(A1:A1,"*AC*"),COUNTIFS(A1:A1,"*AA*"))

 

[zero269]

Hi Rob,

For conditional formatting using a variety of criteria, I prefer to use a Defined Name (range) as it offers greater flexibility.

Define and use names in formulas - Microsoft Support

Assign a descriptive name to a range of cells (a “named range”), that can be used in formulas.

support.microsoft.com

I use this in one of my Trackers that help me identify a books status. However, I created a SAMPLE using the following data:

Book1
	A	B	C	D
1	Data	Test		Criteria
2	aaa	1		aa
3	aab	2		ab
4	aac	2		ac
5	aad	1
6	slab	1
7	department	0
8	ability	1
9	queen	0
10	category	0
11	space	1
12	bath	0
13	requirement	0
14	region	0
15	signature	0
16	replacement	1
17	love	0
18	relationship	0
19	technology	0
20	aardvark	1
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))

Named Ranges
Name	Refers To	Cells
cfCriteria	=Sheet1!$D$2:$D$4	B2:B20

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))	text	NO

The Data column holds the data you want to conditionally format.
The Test column is being used just to show us how that our code is identifying the data based on the criteria correctly before using it with CF. It also shows you how many times that data meets your criteria which is where the SUM comes into play. This column can be removed after testing.
The Criteria column shows the criteria you want to use for your Conditional Formatting. The use of the Wildcards will be used within the CF Formula later.

You can place your criteria list on another Sheet (even hidden if needed). In order for your Criteria to grow without updating your CF Rule, you'll want to Format that Criteria list into a Table. However, you cannot reference a Table with CF. This is why I have the same range as a Named Range and a Table. I prefixed cf to the name so I know that range is only used for Conditional Formatting.

Now if you need to add some new criteria that fits the same wild card format you're using here, then just add it to the Table and your data will be formatted with this new value. If you remove a Criteria from the list, then those cells will no longer be highlighted.

To test this, I added "ee" to the Criteria Table and the word queen was automatically highlighted.

That's because the Conditional Formatting rule was updated automatically using that Named Range:
You can see that the range now goes down to Row 5 to account for that new criterion that I added.

Book1
	A	B	C	D
1	Data	Test		Criteria
2	aaa	1		aa
3	aab	2		ab
4	aac	2		ac
5	aad	1		ee
6	slab	1
7	department	0
8	ability	1
9	queen	1
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))

Named Ranges
Name	Refers To	Cells
cfCriteria	=Sheet1!$D$2:$D$5	B2:B9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))	text	NO

You can check out this article that goes over a few other ways...

Wildcard with COUNTIFS or SUMIFS

You can create elaborated conditions with the function SUMIFS or COUNTIFS with wildcard or greater and lower test.

www.excel-exercise.com

 

[robtyketto]

This should do the job.

=OR(COUNTIFS(A1:A1,"*AB*"),COUNTIFS(A1:A1,"*AC*"),COUNTIFS(A1:A1,"*AA*"))

Thanks, I did use the OR as part of experimenting without the COUNTIFS , perfect simple solution.
Just tested it, all good. Was a little confused as used COUNTIFS with all the criteria is the same set of brackets, useful to know this.

 

[robtyketto]

Hi Rob,

For conditional formatting using a variety of criteria, I prefer to use a Defined Name (range) as it offers greater flexibility.

Define and use names in formulas - Microsoft Support

Assign a descriptive name to a range of cells (a “named range”), that can be used in formulas.

support.microsoft.com

I use this in one of my Trackers that help me identify a books status. However, I created a SAMPLE using the following data:

Book1
	A	B	C	D
1	Data	Test		Criteria
2	aaa	1		aa
3	aab	2		ab
4	aac	2		ac
5	aad	1
6	slab	1
7	department	0
8	ability	1
9	queen	0
10	category	0
11	space	1
12	bath	0
13	requirement	0
14	region	0
15	signature	0
16	replacement	1
17	love	0
18	relationship	0
19	technology	0
20	aardvark	1
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))

Named Ranges
Name	Refers To	Cells
cfCriteria	=Sheet1!$D$2:$D$4	B2:B20

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))	text	NO

The Data column holds the data you want to conditionally format.
The Test column is being used just to show us how that our code is identifying the data based on the criteria correctly before using it with CF. It also shows you how many times that data meets your criteria which is where the SUM comes into play. This column can be removed after testing.
The Criteria column shows the criteria you want to use for your Conditional Formatting. The use of the Wildcards will be used within the CF Formula later.

You can place your criteria list on another Sheet (even hidden if needed). In order for your Criteria to grow without updating your CF Rule, you'll want to Format that Criteria list into a Table. However, you cannot reference a Table with CF. This is why I have the same range as a Named Range and a Table. I prefixed cf to the name so I know that range is only used for Conditional Formatting.

View attachment 84351​

Now if you need to add some new criteria that fits the same wild card format you're using here, then just add it to the Table and your data will be formatted with this new value. If you remove a Criteria from the list, then those cells will no longer be highlighted.

To test this, I added "ee" to the Criteria Table and the word queen was automatically highlighted.

View attachment 84352​

That's because the Conditional Formatting rule was updated automatically using that Named Range:
You can see that the range now goes down to Row 5 to account for that new criterion that I added.

View attachment 84354​

Book1
	A	B	C	D
1	Data	Test		Criteria
2	aaa	1		aa
3	aab	2		ab
4	aac	2		ac
5	aad	1		ee
6	slab	1
7	department	0
8	ability	1
9	queen	1
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))

Named Ranges
Name	Refers To	Cells
cfCriteria	=Sheet1!$D$2:$D$5	B2:B9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=SUM(COUNTIF($A2,"*"&cfCriteria&"*"))	text	NO

You can check out this article that goes over a few other ways...

Wildcard with COUNTIFS or SUMIFS

You can create elaborated conditions with the function SUMIFS or COUNTIFS with wildcard or greater and lower test.

www.excel-exercise.com

Thanks, very useful to know another method.
Will try this method sometime soon I'm sure, I'm typically in T-SQL but use Excel with end users occassionaly to explain data.