I realize there are similar questions to mine, but they have not the answer I seek. I want to use the COUNTIF( L5 , 0 ) function to change the color of a diagonal selection of cells. But Google Sheets only colors one cell of my selection. See picture.

enter image description here

I intend cells D17 and all cells diagonally right and down in a line to become black when cell L5 has a value of 0 entered. For those curious, I'm trying to make a consonant cluster sorter for my conlang projects.

1 Answer 1



The formula you provided evaluates correctly for cell D17 because it is the first cell in the specified formatting range. But for E18 the condition will change to =COUNTIF(M6,0), for F19 to =COUNTIF(N7,0), and so on.

To avoid this and use the same formatting condition for all specified cells, use the absolute address of cell L5 - =COUNTIF($L$5,0) in the formula

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.