Conditional formatting with multiple criteria

Hello,

My dataset is as follows:

Category	Total
Primary	500
Secondary	200
Tertiary	300

I am trying to highlight the total column with three different colors based on the value in the Category and Total columns.

For example, I want to highlight green if the category is Primary and the total is >= to 500, Yellow if >= to 400, or Red if less than 400.
I also want to do the same for the Secondary and Tertiary categories with Green, Yellow, or Red, but the determining values would be different.

I'd like to use icons for the different colors. I was able to do that using one category since I did not have to check what the category was, but with more than one category I haven't been able to do it.

Is there a way this can be done?

Thanks

I missed the discouragement regarding multiple website posting. Here's the URL to the one other location I posted the same request: reddit.com/r/excel/comments/cj6ldq/conditional_formatting_with_multiple_criteria/

Welcome to the forum.

You need three rules for the Total column using the cell value option in the CF dialog (see below).

I am having a bit of trouble visualising what you want for the category.

A word of caution: be sparing with conditional formatting. Overloading your workbook with it can make it hard to read and will eventually slow the workbook down. Better to use it to pinpoint relevant data rather than to colour-code everything, which is overkill.

Thanks for the fast response.

This is a performance spreadsheet for three different teams. Each team has different parameters for being Green, Yellow, or Red. What you have above is what I used when it was just one team.

So, imagine the requirement being as follow:

Team 1: Score 500 or above = Green, equal or greater than 400 but less than 500 = Yellow, less than 400 = Red
Team 2: Score 400 or above = Green, equal or greater than 300 but less than 400 = Yellow, less than 300 = Red
Team 3: Score 300 or above = Green, equal or greater than 200 but less than 300 = Yellow, less than 200 = Red

Ideally, I am looking for something like this as the condition to highlight the cell as Green. The formatting condition for cell B2 would be something like this.... =IF($A$2="Primary",500,If($A$2="Secondary",400,300)).

Causion acknowledged. I used the icons to make it easier to read.

Attached is what I had working for just one team. But with now three teams, I am just not sure how or if it is doable.

sample.jpg

You probably need to set up a lookup table for the teams so that Excel knows which scoring system to use. However, you can't use formulae that refer to lookup tables with icon sets.

My initial reaction is that you are over-complicating things, and that your resulting workbook will be an overload on the senses.

I suggest you provide a sample workbook if you need any further help with this.

Yes, you are allowed to attach a workbook.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

A lookup is what I intend to use as soon as I find out how to do the conditions. Then I'll point to the lookup.

But not with icon sets ...

I have told you how to create conditions. However, with multiple value sets, this won't work, so the CF formula will have to be the lookup. Therefore I cannot tell you how to do the CF until you tell me what the lookup will be. You seem to be getting this a bit muddled.

The lookup as you are showing it won't work. Attach the workbook, please.

I think I may be misunderstanding you regarding lookup. My screenshot wasn't meant to be a lookup, just a reference of how I want the conditions to behave.

Could you elaborate or show me an example?

Still waiting to see the workbook.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

The purpose of a sample workbook is to show us what you want - please add some sample data with the expected formatting manually applied (using colour fills, etc.).

I am still no closer to being able to visualise what the result of all this should look like. You know because it's in your head, but I don't - sorry.

How many teams will there be in the real workbook?

Did you try the format painter?

1. Select C2:G2.
2. Click Format Painter.
3. Select C3:G11.

I did try the format painter, but for it to work properly I would need to update my if statement that calls for $B$2 to $B2, and then use the format painter. But when I update the if statements, I get this error.

error.jpg

If somehow the format painter was able to refer to the team name of its respective row, it would work. But all rows point to $B$2.

There are three teams for now, but there's potential for more. I twill not be A LOT more, but I expect it to increase to no more than 5.

I do have an idea, but it involves helper columns - is that feasible?

You wouldn't need to. Leave it with me - let me see if I can make it work first.

OK - so I've mocked up a basic example. The idea is that you have two columns for each day (you could make these look like one with formatting).

The first column houses a formula that uses the lookup table on the right:

=IFERROR(--INDEX(Table1[[#Headers],[0]:[3]],MATCH($D2,OFFSET(Table1[[#Headers],[0]],MATCH($B2,Table1[Team],0),0,1,4),1)),"")

This is then set to use the result of the formula to trigger the icon (which is set to show alone in the cell without the cell contents).

If you want icon sets, then this is the only way, I think.

Thanks - I like the trick you used. On the real excel, I am using a reference table as well. It'll take me a little time to dissect what you did, but looking at the formula and whatnot, I understand it. The only challenge though is this excel requires sorting on a daily basis, and using two columns per day is workable but may be challenging. Also, this spans over 14 days and repeats (on a new wokrsheet), so it will cause the excel to be wider than I'd like. Screenshots are taken and posted on a daily basis, so the wider the excel the smaller the view has to be adjusted. The length is usually 20 rows of data per team.

On the bright side, the way you have it is very clean, and having the color indicators separate from the value column is an excellent idea. I will play with the excel to see what works best.

By the way, I don't have to use icon sets. I am only using it currently because the excel "looks" cleaner with them. I am not opposed to using cell highlights or something else that works. I wasn't able to do it with highlighting the cells either, at least not with the if formula.

Also, what does this mean?

rep.jpg

That means what it says: you cannot award me any more rep points until you have given some to someone else. Nice thought, but don't worry about it!

Let us know how you get an. In terms of flagging using just colour, you'd recycle the formula I've used in the helper column within the CF. Let us know if you need any more help.

That makes no sense, but rules are rules I suppose.

It's a measure to prevent artificial inflation of reputations, which has happened in the past. It is what it is - it's the thought that counts. Thanks!

I will have another look at your issue now.

I've just tried to apply the formula to conditional formatting and it is not being accepted. I'll keep playing and see if I can get something to work.

I've drawn a blank on this - sorry. I've put out a call for help to the wider community.

Thanks, Jason - I hadn't thought of R1C1 references.

Thanks Jason - I downloaded your attachment and saw your changes, but I do not see any difference though. Only the first row is working. The values on B3:B11 have no impact on their corresponding row, and B2 still dictates the behavior of the entire table (C2:G11).

I copied and pasted the formatting formula for green in N2:N11, and the returned values are correct. I did the same for the yellow formula in O2:011, and the returned values are also correct. I changed the team names in B2-B11, and the values from N2:011 changed accordingly. Based on that, I deduce that the formatting SHOULD work, but for some reason it is working.

Something else I notice is when I use the Edit Formatting rule window for the Green or Yellow condition, even just for viewing purposes, the formula for other disappears.

Then there's the approach I suggested back in post #17 - maybe that's not looking quite so infeasible now.

Originally Posted by jason.b75

Using similar formulas as conditional formatting rules to change the cell background colour instead of using icons.
Formula for green would be

Formula:

Please Login or Register  to view this content.

changing the lookup column to 3 for yellow and using =C2>0 for red.
The rules should be in order, green, yellow, red. With 'Stop if True' checked for green and yellow. If you get this bit wrong it will probably all go red.

This works. I'll use light colors to make it less busy and easier on the eye.

I don't understand why your RICI reference did not work though. It should have worked....

Originally Posted by jason.b75

Or apply an icon rule to each individual row of data instead of one covering the whole table (would be a slow and painful process manually, but should be possible to apply with vba).
If you want to try this then I'll make up a test file for you later (probably not until friday evening, UK time, but will do before if I get chance).

thanks - it doesn't need to be that complex. Using the cell highlights will do what I need.