Forum Discussion
Conditional Formatting Help
So, mandatory locations are defined somewhere in Excel
let say in L1:L6. Actually it could be in another hided sheet, doesn't matter. Let add in J3 formula
=SUM(COUNTIF($L$1:$L$6,B$3:I$3))and drag it down till end of the range. It returns 6 if all mandatory locations are listed in the row, less number otherwise.
After that apply conditional formatting with icons set to our cells in column J
In rule description
select Type Number, set 6 for first value and zero for next one; select from drop down list desired icons for first two; and select Show Icon Only.
SergeiBaklan Sorry about that, the mandatory locations are listed as P3, ED 1, ED 3, E POD, NB 1, and NB 2. The ones listed as RV or C7 are optional.
So, mandatory locations are defined somewhere in Excel
let say in L1:L6. Actually it could be in another hided sheet, doesn't matter. Let add in J3 formula
=SUM(COUNTIF($L$1:$L$6,B$3:I$3))
and drag it down till end of the range. It returns 6 if all mandatory locations are listed in the row, less number otherwise.
After that apply conditional formatting with icons set to our cells in column J
In rule description
select Type Number, set 6 for first value and zero for next one; select from drop down list desired icons for first two; and select Show Icon Only.
SergeiBaklan I worked through the steps you provided (which are very well explained, thank you), but my formula always returns a 0. I copied the formula you posted exactly, and tried altering it a few different ways, always with the same result. Do you happen to know why this may be?
Sorry didn't mentioned that - if you open my file the formula is in curvy brackets {} (for non-DA Excel), which means it shall be entered as array formula, i.e. with Ctrl+Shift+Enter.
Perhaps it will be easy to use
=SUMPRODUCT(COUNTIF($L$1:$L$6,B$3:I$3))it works as regular formula.