Solved

# Amend countif to include extra step

Posted on 2012-09-14
378 Views
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
Question by:route217

LVL 31

Expert Comment

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

LVL 50

Accepted Solution

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

LVL 31

Assisted Solution

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

LVL 50

Expert Comment

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

LVL 31

Expert Comment

Hi teylyn,

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

Cheers
Rob
0

## Featured Post

### Suggested Solutions

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.