Conditional format usign COUNTIFS

Closed
miter69 Posts 2 Registration date Monday July 20, 2009 Status Member Last seen July 22, 2009 - Jul 21, 2009 at 11:22 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 22, 2009 at 07:59 PM
Hello, Ive got a column of cells I would like to conditionally format
based on 3 criteria pulled from 3 different columns. I tried using the counifs formula I used to create the number as a conditional format...however it highlights a large number of cells...but it doesnt meet the criteria set forth in the counifs formula....PLEASE HELP!

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 21, 2009 at 09:14 PM
sorry for this simple question. where did you type in the formula
when you click format menu -conditional formula you get conditional format window
under "condition1"
there is a small arrow click that arrow and choose
"formula is"
and then write the formula
if you are going to copy the formatting down you should not have any dollar signs depending upon the condition
1
miter69 Posts 2 Registration date Monday July 20, 2009 Status Member Last seen July 22, 2009 1
Jul 22, 2009 at 07:38 AM
Its Excel 2007, so I typed the formula in under conditional format --> New rule --> use a formula to determine which cells to format. This is the exact formula I used under the conditional format......=COUNTIFS(E2:E179,"1",N2:N179,"Y",P2:P179,TRUE) This formula works just fine on its own...that is without trying to use it as a conditional format, and basically all I would like to do is figure out which cells its counting to come up with the number. That is to say that the end result of the formula equals 11, and I would like to know where its getting the 11 from.
1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 22, 2009 at 07:59 PM
COUNTIFS(E2:E179,"1",N2:N179,"Y",P2:P179,TRUE)
this gives number of occasions when in column E, 1 occurs and in column N "y" occurs and in column P TRUE occurs. ok that is 11 . on 11 occasions simultaneously these critera occur.

But in conditional format you have to give one more condition
for e.g. if this number is more than 5 color the cell. then in the condition format formula you must write

=COUNTIFS(E2:E179,"1",N2:N179,"Y",P2:P179,TRUE) >5

Hope I have made this clear
1