Conditional formatting of a cell based on value in 4 adjacent cells

microsoft excelmicrosoft-excel-2007microsoft-excel-2010worksheet-function

I'm trying to get a cell C3 to highlight using conditional formatting, but based on a value in 4 other cells.

If 4 continuously adjacent cells (e.g. AS3, AT3, AU3, AV3) contain the X value only then do I want the cell C3 to highlight, so NOT if they are not joined together.

(It's a worksheet to highlight absences, 4 times in a row).

How would I go about this?

Best Answer

Let's assume your range of data (with possible "x"s) is in D3:EC3 then use this formula in conditional formatting

=COUNTIFS(D3:DZ3,"x",E3:EA3,"x",F3:EB3,"x",G3:EC3,"x")

because it offsets each range by 1 it will only give a positive count when you have a run of at least 4 consecutive "x"s in that range. A positive number triggers the conditional formatting

Related Question