Count, Sum, and Filter by Color
 

Dear Experts;

My data is consist around 100 row, most them highlited with different colors;

I need to do the following; Please help me

1.. Count by Color
2.. Sum by color
3.. Filter by color

thanks in advance
khalil

Count, Sum, and Filter by Color
 

Excel version ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Khalil" wrote in message ...
Dear Experts;

My data is consist around 100 row, most them highlited with different colors;

I need to do the following; Please help me

1.. Count by Color
2.. Sum by color
3.. Filter by color

thanks in advance
khalil

Count, Sum, and Filter by Color
 

See Chip Pearson's site for working with the colorindex of cells.

http://www.cpearson.com/excel/colors.aspx


Gord Dibben MS Excel MVP

On Fri, 19 Jun 2009 08:12:01 -0700, Khalil
wrote:

Dear Experts;

My data is consist around 100 row, most them highlited with different colors;

I need to do the following; Please help me

1.. Count by Color
2.. Sum by color
3.. Filter by color

thanks in advance
khalil

Count, Sum, and Filter by Color
 

Hi,

You can do Filter by Color in Excel 2007.

For count and sum by color you will need a custom VBA function, and the
function will depend on the version of Excel you are using.


Here is a custom function to count cells base on font color and fill color:

Function CountFormats(R As Range, E As Range) As Integer
Dim cell As Range
Dim Total As Integer
Application.Volatile
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
With cell
If .Interior.ColorIndex = S.Interior.ColorIndex _
And .Font.ColorIndex = S.Font.ColorIndex Then
Total = Total + 1
End If
End With
Next cell
CountFormats = Total
End Function

In the spreadsheet you enter =countformats(A2:B7,D2) Where A2:B7 is the
range you want to check and D2 is a cell formatted to the desired format.

To add this code to a workbook press Alt+F11 and select your file in the
Project explorer in the top left side of the screen. Choose Insert, Module.
Put the code in the resulting module.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Khalil" wrote:

Dear Experts;

My data is consist around 100 row, most them highlited with different colors;

I need to do the following; Please help me

1.. Count by Color
2.. Sum by color
3.. Filter by color

thanks in advance
khalil