CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Thomas
Thomas

Reputation: 13

Excel Conditional Formatting - Combinations

I have a spreadsheet roster. Each row represents a different individual in an organization that met once each month this fall (4 meetings total).

We used Excel to keep track of meetings, so there's 4 columns: "September", "October", "November", and "December". Whenever the individual attended a meeting, we put an x under the proper month.

We have other columns, too, and I'm familiar on using conditional formatting with other columns. The requirement for the organization was that individuals had to attend at least 3 out of the 4 meetings in the fall, but it doesn't matter which months they chose to attend.

If the row has 3 xs in whatever combination over the 4 meeting columns (to represent having attended 3 meetings, whichever months they were), how do I make the entire row highlight green? I know how to do conditional formatting with simpler tasks, but am stumped... how do I code for at least 3 out of 4 boxes to have an x, regardless what combination of columns the 3 xs come from?

Thank you very, very much for helping. It means a lot!

Upvotes: 1

Views: 143

Answers (1)

Ben I.
Ben I.

Reputation: 1082

So, if your four x values were in columns C to F, beginning from row 2, you could highlight row 2 and apply the following conditional formula:

=COUNTA($C2:$F2)>=2

Then choose what you would like the conditional formatting to look like.

Finally, use the format application tool (in the home tab, it looks like a paintbrush), and apply that row to any other rows you would like.

The key to understanding this formula is the $ character, which ensures that the formula will stay in a fixed Column, even when applied to other cells. If we had written =COUNTA($C$2:$F$2)>=2, then the rows would also have been fixed, and every cell's formatting would depend entirely on the xs in row 2.

Upvotes: 0

Related Questions