+ Reply to Thread
Results 1 to 8 of 8

Count cell background colour

  1. #1
    Registered User
    Join Date
    03-18-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    68

    Count cell background colour

    Hi,

    I need to count the cells which has Green, Red and Yellow colour in excel sheet, basically the cell colour i have used with conditional formatting ...

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count cell background colour

    Simply replicate the Conditional Formatting logic in your count based calculations.

    If you need more help - please answer the following:

    What are the Conditional Formatting rules ?

    Where are the cells of interest ?

    Where are the results to be located ?

  3. #3
    Registered User
    Join Date
    03-18-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    68

    Re: Count cell background colour

    Hi Ote,

    Here are my answers!

    What are the Conditional Formatting rules ?
    -- If the cell value is ="0" then green, if cell value is less than zero is red, if cell value is great than zero is yellow

    Where are the cells of interest ?
    -- the syntax is simple as above, there is no specific reference being used


    Where are the results to be located ?
    I just need a number of count for each colour in a selected cells there were around 31 rows and 5 columns

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count cell background colour

    By cells of interest I meant those that contain the Conditional Formatting...

    If the range (31 rows & 5 columns) is contiguous then it should be as simple as:

    =COUNTIF(A1:E31,0)

    =COUNTIF(A1:E31,"<0")

    =COUNTIF(A1:E31,">0")

    modify range to suit of course

    If the ranges are non-contiguous then post back with more detail.

  5. #5
    Registered User
    Join Date
    03-18-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    68

    Re: Count cell background colour

    wow, perfect solution to my query, but one question, countif is not calculating properly for "0" value, is there anything wrong with it or any contraints

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count cell background colour

    The below:

    =COUNTIF(A1:E31,0)

    would count zeroes irrespective of whether the values were stored as numbers or numbers stored as text (implied by ="0")

    Could you perhaps post one of the formulas you're using to generate the "0" ?

  7. #7
    Registered User
    Join Date
    03-18-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    68

    Re: Count cell background colour

    Hello Ote,

    Its simple formula i am using it between two cells, like for example, A2-B2 in both cells the value contains is 3 ..so the value is zero, similarly i had done for rest of the cells.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Count cell background colour

    Based on your posts there's no reason as to why the 0 would not Count.

    You will need to post a sample which illustrates the problem in action.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1