• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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..
0
route217
Asked:
route217
  • 3
  • 2
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)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

screenshot
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Rob,

some comments:

>> 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
 
Rob HensonIT & Database AssistantCommented:
Hi teylyn,

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

Cheers
Rob
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now