Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filter by color | Excel Discussion (Misc queries) | |||
Filter by Color | Excel Worksheet Functions | |||
Filter by Color | Excel Discussion (Misc queries) | |||
Filter by color of row | Excel Worksheet Functions | |||
Filter by color of row | Excel Worksheet Functions |