Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for summing or counting cells based on a specified fill color
Hi
I would like to create and run a function that Sums or counts cells based on a specified fill color, for e.g. say i have a green fill in Cell B1 and I have different fill colours in Range (A1:A10) I would like to know how many cells in (A1:A10) have the same green fill as in Cell B1 Thanks San |
#2
|
|||
|
|||
Quote:
Hope this helps |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for summing or counting cells based on a specified fillcolor
Hi San
Here's something quick n dirty, but it does what you're looking for. It's essentially requires you to employ helper cells to do the math. A1 is the cell in which you change the color you wish to count. Adjust the ranges to suit your requirements. Sub CountColor() Dim cRange As Range, cSum As Range Dim cCell As Range, tCell As Range Dim c As Range Set cRange = Range("C1:C10") 'your color range Set cCell = Range("A1") 'base color to count Set cSum = Range("D1:D10") 'apply number to matching color Set tCell = Range("B1") 'Sum Total matching colors For Each c In cRange If Not c.Interior.Color < cCell.Interior.Color Then c.Offset(0, 1).Value = 1 End If Next c tCell = WorksheetFunction.Sum(cSum) End Sub Someone else may be able to give it a crew-cut to improve. HTH Mick. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function for summing or counting cells based on a specified fill color
On Tuesday, February 26, 2013 1:05:50 PM UTC+5:30, shriil wrote:
Hi I would like to create and run a function that Sums or counts cells based on a specified fill color, for e.g. say i have a green fill in Cell B1 and I have different fill colours in Range (A1:A10) I would like to know how many cells in (A1:A10) have the same green fill as in Cell B1 Thanks San Thanks a lot guys for your help San |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in counting and summing cells based on multiple conditions | Excel Worksheet Functions | |||
Summing cells within a column based on cell fill color | New Users to Excel | |||
change fill color of a range of cells based on color of a cell? | Excel Programming | |||
color fill cells based on IF function or formula | Excel Worksheet Functions | |||
Counting/summing cells based on background color | Excel Programming |