ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Function for summing or counting cells based on a specified fill color (https://www.excelbanter.com/excel-worksheet-functions/448264-function-summing-counting-cells-based-specified-fill-color.html)

shriil February 26th 13 07:35 AM

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


Gizzmo February 26th 13 09:25 AM

1 Attachment(s)
Quote:

Originally Posted by shriil (Post 1609782)
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

See the attached file code has been sourced from http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm

Hope this helps

Living the Dream February 26th 13 11:37 AM

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.


shriil March 2nd 13 05:02 AM

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


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com