Conditional Format - COUNTIF Range to a Criteria Range

[Vesp]

Hi MrExcel, I have been scouring the board this morning and cant seem to find a solution to what I need.

I have a large data worksheet, reference worksheet and a search sheet i am using to lookup data.

The lookup sheet has a drop down tab of all people. based on your selection Column E3:E100 populate with all Model numbers of various products they own.

Cells B5:B9 has a list of every type of product required for the person selected, this list changes product types using an INDEX and Match function.

My reference sheet identifies what model numbers belong to what product type in separate columns. So Product Type 1 model numbers will always be in Reference!B2:B25, Product Type 2 will always be in Reference!C2:C25, ect

I want to conditionally format B5:B9 to check if the product types they require is on the list of their owned products.

to do this i have done the following =IF(COUNTIF(B5:B9,"Product Type 1"),IF(COUNTIF(E2:E100,Reference!B25),TRUE,FALSE))
However this will only check 1 record on the reference sheet at a time.
I want to use something like =IF(COUNTIF(B5:B9,"Product Type 1"),IF(COUNTIF(E2:E100,Reference!B2:B25),TRUE,FALSE))
But this does not work as the Criteria of the COUNTIF is a range. Is their an alternative function i could use to do this?

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you show us a smallish snippet of the Reference sheet and explain in words which cells in B5:B9 in your sample above should be identified with the Conditional Formatting & why?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Vesp]

Hi Peter_SSs thanks for the help. Unfortunately our work computers are quite restricted due to security concerns and I cannot install addons. I have created a sample reference sheet to help explain better. I also realized i need to modify the condition to allow for multiple colours.

I want B5:B9 to colour based on if the person has a product or not.

-If the Product type matches a Model number in E2:E100 color green (using reference table to determine what models are what type of product)
-If the Person has no models that match a product it should color red indicating they do not have one

So the formula would be something like
=IF(COUNTIF(B5:B9,"Laptop"),AND(IF(COUNTIF(E2:E100,Reference!B2:B12),TRUE,FALSE)) - Format Laptop to be Green if a model in E2:E100 matches the reference sheet
=IF(COUNTIF(B5:B9,"Laptop"),AND(NOT(IF(COUNTIF(E2:E100,Reference!B2:B12),TRUE,FALSE)))) - Format Laptop to be Red if no cells in E2:E100 match the laptop reference sheet

=IF(COUNTIF(B5:B9,"Monitor"),AND(IF(COUNTIF(E2:E100,Reference!C2:C12),TRUE,FALSE)) - Format Monitor to be Green if a model in E2:E100 matches the reference sheet
=IF(COUNTIF(B5:B9,"Monitor"),AND(NOT(IF(COUNTIF(E2:E100,Reference!C2:C12),TRUE,FALSE)))) - Format Laptop to be Red if no cells in E2:E100 match the laptop reference sheet


I will need to create a conditional format for B5:B9 for each Product Type to colour Red/Green based off the model list in E2:E100


Lookup Sheet example:
Laptop is Green as they have 1 match
Monitor is Green as the have 2 matches
Vehicle is Green as they have 1 match
Phone is Red as they have 0 matches

Reference:

Let me know if that is enough info. If not i will try create a sample sheet on a personal computer that i can provide.

 

[Peter_SSs]

Unfortunately our work computers are quite restricted due to security concerns and I cannot install addons.

Fair enough. In the future best to mention that in post 1 to stop us continually asking.
For small samples like you had, you can simply copy/paste from Excel in to the forum an explain what the range is.

I will need to create a conditional format for B5:B9 for each Product Type to colour Red/Green based off the model list in E2:E100

If I have understood correctly, I don't think so.

You may have to adjust the ranges to suit your data but see if this is headed in the right direction.

Vesp.xlsm
	B	C	D	E	F
1	Laptop	Monitor	Vehicle	Phone	Prod 5
2	x	q	qq	z1	Pr-1
3	r	b	ww	z2	Pr-2
4	t	p	ee	z3	Pr-3
5	a	l	rr	z4	Pr-4
6	q	z	c	z5	Pr-5
7	w	m	yy	z6
8		k	uu	z7
9		d	ii
10		g
11
Reference

Vesp.xlsm
	B	C	D	E
1
2				Model
3				a
4	Product			b
5	Laptop			c
6	Monitor			d
7	Vehicle
8	Phone
9	Prod 5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B9	Expression	=COUNT(MATCH(FILTER(Reference!B$2:F$100,Reference!B$1:F$1=B5,""),E:E,0))=0	text	NO
B5:B9	Expression	=COUNT(MATCH(FILTER(Reference!B$2:F$100,Reference!B$1:F$1=B5,""),E:E,0))>0	text	NO

 

[Vesp]

Great solution, Thanks Peter!

works perfectly. i was concerned due to B5:B9 values changing based on the person selected the formula may get confused but your solution using FILTER 'include' was just what I needed.

 

[Peter_SSs]

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