Conditional formating

[excellahuntress]

So I have 2 charts.
One looks like this.

Color	Apple	Orange	Grape
Blue	4	0	1
Red	5	4	0
Green	0	3	0

I want to be able to highlight the 2nd chart so that any color that occurs MORE THAN 3 times on a fruit from chart 1, are highlighted like this.

Blue	Apple
Blue	Grape
Red	Apple
Green	Orange
Blue	Apple
Red	Apple
Blue	Apple
Red	Orange

Green	Orange
Red	Apple
Blue	Apple
Red	Orange
Green	Orange
Red	Orange
Red	Apple
Red	Apple
Red	Orange

So the 4th blue apple would be highlighted but not the other blues. The 4th & 5th red apple, the 4th red orange would be highlighted. No green would be highlighted.

 

[arthurbr]

Select the range and use

=SUMPRODUCT(--($A$1:$A1=$A1),--($B$1:$B1=$B1))>3

as CF
Format as needed and adapt ranges

 

[Peter_SSs]

Does the first table actually have anything to do with the question? If so, can you clarify how it applies?

 

[excellahuntress]

Select the range and use

=SUMPRODUCT(--($A$1:$A1=$A1),--($B$1:$B1=$B1))>3

as CF
Format as needed and adapt ranges

Thank you!!!!

 

[Peter_SSs]

Thank you!!!!

Sounds then like the first table was not relevant to the CF in which case you could use the more efficient COUNTIFS in your CF formula should you want.

=COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)>3

 

[arthurbr]

What's more efficient about COUNTIFS ?

 

[Peter_SSs]

What's more efficient about COUNTIFS ?

It is simply a more efficient (faster) algorithm developed by Microsoft for the newer function.
If you want to test the speed of the functions themselves, remove any conditional formatting and just copy the 17 rows of sample data and paste to, say, 17,000 rows in columns A:B

In C1: =SUMPRODUCT(--($A$1:$A1=$A1),--($B$1:$B1=$B1))>3
In D1: =COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)>3

Copy C1:D1 down the 17,000 rows.

Use a range timer to measure calculation times (eg timer code provided by Microsoft here, about half way down the linked page)
The actual time will depend on the user's system resources but for relative time differences for me
- the time to calculate C1:C17000 was 14.7 seconds
- the time to calculate D1:D17000 was 9.2 seconds

That is, a 37+% increase in efficiency using COUNTIFS as compared to SUMPRODUCT.

 

[arthurbr]

Good to know. Thanks. But I'll stick to SUMPRODUCT which is much more versatile IMO ( even if a little more complicated to use)
I can't check my system as you suggested as I am running Excel on Ubuntu but I'll take your word for it

 

[excellahuntress]

Sounds then like the first table was not relevant to the CF in which case you could use the more efficient COUNTIFS in your CF formula should you want.

=COUNTIFS($A$1:$A1,$A1,$B$1:$B1,$B1)>3

Sorry I'm fairly new to excel. Everything I know has been picked up on online searches in the last year as I run into problems so there's a lot of functions I do not know. To my untrained eyes I thought I needed both charts for calculation... thank you both for your amazing answers.

 

[Peter_SSs]

Good to know. Thanks. But I'll stick to SUMPRODUCT which is much more versatile

No problem. I agree that SUMPRODUCT is versatile & can be used in some circumstances easily where COUNTIFS cannot but I don't think it should be a matter of choosing one of them and then always using that one. In my view better to use a simple, fast function if it is applicable - especially if the data set is large.

thank you both for your amazing answers.

You're welcome. Glad we could help. Thanks for the follow-up.
You have a couple of choices for this task.