Count, Sum, and Filter by Color

K

Khalil

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
 
S

Shane Devenshire

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top