Displaying a 1 in conditional formatting rules

[Stef9910]

Hello all,

I have received some fantastic help from awoohaw, PeteWright and fluff in recent days and one more problem to conquer.

Is it possible to add to the conditional format rules, =AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64)) to have the number 1 appear in the cells?

This is so I can total the number cells that display the number 1 throughout the spreadsheet.

Your help will be greatly received.

Thank you

Stefan

 

[arthurbr]

You can't use CF for that. You'll need an extra column with a formula like

=--(AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64)))

 

[Stef9910]

You can't use CF for that. You'll need an extra column with a formula like

=--(AND(N$62>=$B64,N$62<=$D64,ISNUMBER($B64)))

Thanks arthurbr, is there a way to count the cells that are colored by the conditional formatting, there are two colors, green and red?

 

[Peter_SSs]

is there a way to count the cells that are colored by the conditional formatting, there are two colors, green and red?

It can depend on how the conditional formatting is applied, but you would normally use the same conditions for the count as for the conditional formatting.
What are the two conditional formatting rules for the green and the red?

 

[Stef9910]

It can depend on how the conditional formatting is applied, but you would normally use the same conditions for the count as for the conditional formatting.
What are the two conditional formatting rules for the green and the red?

Thanks for reply Peter, there are actually three rules, but only two colors,

=AND(X$13>=$N15,X$13<=$P15) displays a green cell
=AND(X$13>=$L15,X$13<=$N15,ISNUMBER($L15)) displays a red cell
=AND(X$13>=$L15,X$13<=$R15,ISNUMBER($L15)) displays a red cell

 

[arthurbr]

The "red" condition can be trimmed down to

=AND(X$13>=$L15,OR(X$13<=$N15,X$13<=$R15),ISNUMBER($L15))

 

[Peter_SSs]

Thanks for reply Peter, there are actually three rules, but only two colors,

=AND(X$13>=$N15,X$13<=$P15) displays a green cell
=AND(X$13>=$L15,X$13<=$N15,ISNUMBER($L15)) displays a red cell
=AND(X$13>=$L15,X$13<=$R15,ISNUMBER($L15)) displays a red cell

And what range are those conditional formatting rules applied to?
In other word, what range are you trying to count the conditional formatted green and red cells in?

 

[Stef9910]

And what range are those conditional formatting rules applied to?
In other word, what range are you trying to count the conditional formatted green and red cells in?

The range I have in the rules is X15 to BF157 (=$X$15:$BF$157)

Thank you for your help Peter

 

[arthurbr]

Are you interested in post #6?

 

[Stef9910]

Are you interested in post #6?

Thank you for your reply arthurbr, the "red" rules I have work well, and I will be trying your formula, the only reason I have two red rules is because of multiple start and end dates i have in the workbook.

Thank you for your help, I am learning so much from everyone's advice.