Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 26th 13, 08:35 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 37
Default 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   Report Post  
Old February 26th 13, 10:25 AM
Junior Member
 
First recorded activity by ExcelBanter: Feb 2013
Posts: 20
Default

Quote:
Originally Posted by shriil View Post
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
Attached Files
File Type: zip OzGridSumCount.zip (14.7 KB, 35 views)
  #3   Report Post  
Old February 26th 13, 12:37 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 151
Default 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("D110") '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   Report Post  
Old March 2nd 13, 06:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2009
Posts: 37
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help in counting and summing cells based on multiple conditions Dave Excel Worksheet Functions 9 November 13th 09 04:23 PM
Summing cells within a column based on cell fill color Jim D. New Users to Excel 2 October 8th 08 04:56 PM
change fill color of a range of cells based on color of a cell? DarMelNel Excel Programming 0 March 2nd 06 07:35 PM
color fill cells based on IF function or formula Victor Jones Excel Worksheet Functions 1 November 23rd 05 04:17 PM
Counting/summing cells based on background color Nan[_2_] Excel Programming 2 May 11th 04 05:36 PM


All times are GMT +1. The time now is 01:19 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017