Amend countif to include extra step

Posted on 2012-09-14
Last Modified: 2012-09-14
Hi Expert

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


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..
Question by:route217
    LVL 31

    Expert Comment

    by:Rob Henson
    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.

    Rob H
    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


    Change that to be


    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
    LVL 31

    Assisted Solution

    by:Rob Henson
    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.

    Rob H
    LVL 50

    Expert Comment


    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:


    both formulas deliver the same result in the attached file.

    cheers, teylyn
    LVL 31

    Expert Comment

    by:Rob Henson
    Hi teylyn,

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    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.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now