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.
How do we know which locations are mandatory and which are not?
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.