none
How to count cells with certain font color in Excel without using VBA?

    Question

  • I have used cells with certain font color/format in many sheets. In the front sheet i want formula which should count if the cells font is in certain color. say for ex, if the cell A1 in sheet3 is in red color it should be counted in sheet1 where i have applied the formula.

    I need formula without VBA code. Because when I use VBA code i'm not able to save the excel in normal Excel2007 workbook format. It has asked me to save it in macro-enabled workbook format i.e xlsm or in 2003 format.

    Solution to any of the above queries is appreciable.

     

    Thanks,

    Suresh J

    Friday, August 27, 2010 6:06 AM

All replies


  • Bonsour®
    "Suresh J_2010" a écrit

    i want formula which should count if the cells font is in certain color.
    say for ex, if the cell A1 in sheet3 is in red color it should be counted in
    sheet1 where i have applied the formula.

    I need formula without VBA code.

    No VBA ???
    ;o)))
    Use XL4 function !!!

    GET.CELL(24,Target)  get font ColorIndex of target

    How to :
    Select Cell B1
    define Name : FONTCOLORIDX
    refers to : =GET.CELL(24,A1) ' ---Beware relative address no $$
    OK

    Usage
    in  any cell
    =FONTCOLORIDX
    !!! no parenthesis nor parameter
    you get the font colorIndex of the immediate current left cell

    i.e
    G5 with font color Red
    H5=FontcolorIdx  ----->display : 3

    then with colorindex value you are able to calculate related information(COUNTIF, SUMIF, SUMPRODUCT...)

    Warning :
    Change color or font is not an event for calculation
    You must push F9 to enable color change and have valuable results


    Maude Este
    Friday, August 27, 2010 10:28 AM
  • Thank you Maude.

    I have tried the same but for all the color the value is displayed as 1 only. In the Sheet1, there are range that contain values and among them some are colored in Red color. Now I want formula in "B1" cell, that the count of Red color cells in that range.

    Friday, August 27, 2010 11:39 AM
  • Hi All,

    I finally decided to use VBA for counting the colored cells in a sheet. For this I have created a custom function in the module as follows:

    Function countcolor(rng As Range, colorindex As Integer)
    Dim count As Integer
    count = 0
    For Each rw In rng
      If rw.Font.Color = colorindex Then
        count = count + 1
      End If
    Next rw
    countcolor = count
    End Function

    In my excel sheet, I simply using this as formula "=countcolor($E$5:$E$9,255)" for counting red color font cells,  but here one more problem araised. When I changed the font color in the desired range, the formula is not updating automatically. I have to select the cell through "F2" and press "Enter". The "Ctrl + Alt + F9" also not working.

    I cannot edit the formula cells always since I'm gonna use this formula at many places.

    Any help regarding this is appreciable.

    Thanks,

    Suresh J

    Tuesday, August 31, 2010 6:11 AM
  • I have used this and similar formulas successfully, however, after they are saved, closed then opened again.. this message appears when trying to enter the formula "This formula takes no arguments"

    Any suggestions on what I am doing wrong would be greatly appreciated....

    Tuesday, April 12, 2011 12:40 AM
  • Kevin,

     

    I didn't get any error on saved, closed then opened again and entered the formula too.

    Can you tell me exactly what you have tried after opening the excel?

     

    Regards,

    Suresh J


    Regards, Suresh Jaganathan
    Wednesday, April 20, 2011 5:04 PM
  • Hi All,

    I finally decided to use VBA for counting the colored cells in a sheet. For this I have created a custom function in the module as follows:

    Function countcolor(rng As Range, colorindex As Integer)
    Dim count As Integer
    count = 0
    For Each rw In rng
      If rw.Font.Color = colorindex Then
        count = count + 1
      End If
    Next rw
    countcolor = count
    End Function

    In my excel sheet, I simply using this as formula "=countcolor($E$5:$E$9,255)" for counting red color font cells,  but here one more problem araised. When I changed the font color in the desired range, the formula is not updating automatically. I have to select the cell through "F2" and press "Enter". The "Ctrl + Alt + F9" also not working.

    I cannot edit the formula cells always since I'm gonna use this formula at many places.

    Any help regarding this is appreciable.

    Thanks,

    Suresh J

    You need to press "F9" to refresh.
    Friday, June 17, 2011 3:51 AM
  • ASAP Utilities has a defined function, ASAPCOUNTBYFONTCOLOR, to do this.
    Tuesday, March 05, 2013 1:05 PM