OPTION IN 'COUNTIF' COMMAND 1

[trilinga]

I am not familier with writing of Macros in EXCEL.

I have an Excel spreadsheet in which I have data of different categories identified through different colour fills in the respective cells.

If I have to count the number of cells with a specific category, is there any option in countif command to count the cells with same colour fill?

Thanks for the help.

 

[joerd]

Unfortunately, you cannot retrieve the colorindex of a cell with a standard function in Excel, so you have to write your own user defined function. Go to the VB editor (Alt-F11 or Tools/Macro/Visual Basic editor), then insert a module, and write the following code:

Function GetColorIndex(R As Range) As Long
    Application.Volatile
    GetColorIndex = R(1, 1).Interior.ColorIndex
End Function

Go back to your spreadsheet, and you have available a user defined function to get the color of a cell by its index. You can call it as: [tt]=GetColorIndex(A1)[/tt], which will return a number corresponding to the color of cell [A1]. Modify as needed!

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[ACtrafficengr]

You could add a column for a category code, use menu/format/conditional format to color code based on the category code, and COUNTIF the category codes.

------------------------------------------
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail."

Wm. Phelps Eno, ca. 1928

 

[panduru]

Kudos to ACtrafficengr !

A very elegant and appropriate solution, indeed. Thanks.

 

[trilinga]

Thanks Joerd and ACtrafficengr.

The tips were really helpful

Trilinga