# Amend countif to include extra step

Hi Expert

I have a simple countif formula that returns the numbers of
time a number has appeared in a data range.

=Countif(\$B\$2:\$B\$25,"21")

How would you amend if the conditional format colour in cell A2
Is RED the also make the B3 RED....on CF if how ever A2 is not REd then
No background colour..
###### Who is Participating?

Microsoft MVP ExcelCommented:
Hello route,

if you use the Countif formula as the condition for a conditional format, then you can use the "manage rules" command in the conditional formatting drop down to extend the same rule to another cell.

If the "Red" rule currently applies to cell A2, it will look like this in the "Applies to" box

=\$A\$2

Change that to be

=\$A\$2,\$B\$3

and cell B3 will show the same format as cell A2.

If that does not help, please post a sample file and explain in context.

cheers, teylyn
0

Finance AnalystCommented:
Don't understand the link between the COUNTIF and the Conditional Formatting!!

The formula in the cell does nothing toward the CF. A formula can be used in CF to ascertain the need for formatting but it needs to return a TRUE or FALSE, not a number.

What version of Excel are you using?

In Excel 2007 and later there is COUNTIFS where you can specify more criterion.

Thanks
Rob H
0

Finance AnalystCommented:
However, as I mentioned earlier; the COUNTIF doesn't generate a condition, it only generates a result. Comparing that result to something will then generate a condition.

For example =COUNTIF(\$B\$2:\$B\$25,"21")>5 would be TRUE if count is greater than 5.

Also, why are you putting the 21 in quotes? Strictly speaking that would count the number of instances of the text string 21 rather than the number 21.

Thanks
Rob H
0

Microsoft MVP ExcelCommented:
Rob,

>> the COUNTIF doesn't generate a condition, it only generates a result. Comparing that result to something will then generate a condition.

Not quite. The Countif() can be used as such in conditional formatting. If the result is greater than 0 it will equal to TRUE. Any formula that returns an integer greater than 0 will result in a TRUE value.  If it is zero, it will equal to FALSE. A comparison is not necessarily required. See the Countif in the CF of the attached file. Look, Ma, no comparison.
It DOES depend on the circumstances, though, and I agree that the question does not clearly state the connection between the Countif and the conditional format.

With regards to putting the 21 in quotes:

=COUNTIF(B1:B20,21)
=COUNTIF(B1:B20,"21")

both formulas deliver the same result in the attached file.

cheers, teylyn
27864811.xlsx
0

Finance AnalystCommented:
Hi teylyn,

Thanks for the clarification/correction, every days a school day!

Cheers
Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.