Summing a range if a certain background colour

[Guest]

I would like to be able to sum the numnber of times the cell background is
red in a range. I would also like to do it separately for the background
colour yellow, and separately for the background colour bright green. Ideally
I would like to do it without programming code but any solution very welcome.
I am using Excel 2003

Thanks.

 

[Bob Phillips]

You won't do it without programming. See
http://www.xldynamic.com/source/xld.ColourCounter.html for a solution, but
note the constraints.

--
HTH

Bob Phillips

"Re: Inserting an option button in Word"

 

[Guest]

Thanks Bob, I'll give it a try.

You won't do it without programming. See
http://www.xldynamic.com/source/xld.ColourCounter.html for a solution, but
note the constraints.

--
HTH

Bob Phillips

"Re: Inserting an option button in Word"

 

[Mangesh Yadav]

Its not possible through a simple worksheet formula, here's a UDF which does
it.

Function myCount(countRng, refRng)
myCount = 0
For Each cl In countRng
If cl.Interior.ColorIndex = refRng.Interior.ColorIndex Then
myCount = myCount + 1
End If
Next
End Function

Usage:
=myCount($A$1:$A$10,A1)

You have to count in the range A1:A10. The second argument is the reference
range where you specify that cell which contains the required format.

Mangesh




"Re: Inserting an option button in Word"

 

[Guest]

Bob, struggling with suggested solution. Tried pasting in code into sheet and
using sumproduct but Excel won't recognise ColorIndex part of formula. Not
sure what I'm doing wrong. Any help appreciated.

 

[Peter T]

If you really don't want to use vba and it's ok to include a column of
helper cells, try this:

Select a cell offset from A1 relative to your colour cell and helper cell.
Eg, if you want a column that returns colours in cells one column to the
left, start by selecting B1.

Ctrl-F3, Define Names dialog
Names in Workbook: enter a name, eg FillColor
Refers to: =GET.CELL(63, A1)

If you want to return values in cells to the right, select A1 and change A1
in the formula to the appropriate relative offset.

To return the fill colorindex of C3, in D3 enter =FillColor. Copy down.

To count your bright yellow cells, use Countif, eg
=COUNTIF(D28,6)

If you subsequently change the colour format, the formula will not update*.
But it should update with a full recalculate with Ctrl-Alt-F9.
* Some things, like sort, may update the formula.

VBA is more flexible, but the old XLM / name method much faster.

Regards,
Peter T

"Re: Inserting an option button in Word"

 

[Chip Pearson]

You need to paste the VBA procedure at the bottom of the page in
to a module in the VBA Editor. In the formulas on the page,
ColorIndex is a VBA function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Re: Inserting an option button in Word"
in message

 

[Guest]

Thanks Bob, got it working, and to everyone for answers.