Conditional Formatting based on values of table

Question

I would like to turn black a cell if on the table any of the rows match this condition:

Table : A1:G100
Cell to format J1

Condition that I would like to to implement:

IF(AND(A1=0;OR(B1="bob";B1="jack")) 

turn the cell J1 black.

I used the Excel conditional formatting function and I got the result applying the formula only to the first row of my table. How can I format the cell based on the repetition of the formula for each column of the table?

Answer 1

It's an intense formula but if you are looking for an one formula answer strictly for the conditional formatting formula and impacting nothing else, try the following. your formula in the conditional formatting formula should be

=SUMPRODUCT((A:A=0)*(B:B="jack"))+SUMPRODUCT((A:A=0)*(B:B="bob"))>0

The reason for the two separate sumproduct formulas is to capture "jack" and "bob" instances as an inclusive OR which is triggered by the sum of the two being greater than zero (at least 1 instance of "0 and jack" OR "0 and bob" must be true).

Please use this answer ONLY if you have a special purpose for your J1 cell and you cannot afford a logic block (usually on a separate sheet) to colour your cell. Otherwise please refer to @Tom Sharpe's answer. His formula could be contained anywhere and the conditional formatting simply look if that cell is greater than 0.

Answer 2

There is no need to add IF, the condition is already an IF:

add ,A2=0 at the back only if you want to color Number 0 (column A) as well - just change the color to J1 in conditional formatting fill format

=OR(AND($A2=0,OR(A2="jack",A2="bob")),A2=0)

Answer 3

Suggest a custom formula for J1 of

=COUNTIFS(A:A,0,B:B,"bob")+COUNTIFS(A:A,0,B:B,"jack")

and choose black fill colour.