Can excel count values in a range of cells based on the fill color?
(for example you only want it to count the ones that have the fill
color red. Not count the number of red cells but count the values in the
cells)

There is no built-in excel function to do this. But you can use a UDF that
looks at the range and returns the sum of color. But that function will not
recalculate if you change color. Every time you change the color you will
need to recalculate or wait excel to recalculate...

To install the UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=colorsum(A5:A9,D5)
cell D5 will have the fill color which is the query color...to be searched
in the range A5:A9

'To count the number of cells with numeric values
Function ColorSum(varRange As Range, varColor As Range)
Dim arrTemp As Variant, varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = varColor.Interior.ColorIndex Then
If IsNumeric(cell.Text) Then ColorSum = ColorSum + 1
End If
Next
End Function


'If you mean to sum the cell values with matching color then
Function ColorSum(varRange As Range, varColor As Range)
Dim arrTemp As Variant, varTemp As Variant
For Each cell In varRange
varTemp = cell.Interior.ColorIndex
If varTemp = varColor.Interior.ColorIndex Then
ColorSum = ColorSum + cell.Value
End If
Next
End Function


If this post helps click Yes
---------------
Jacob Skaria


"mike" wrote:

Can excel count values in a range of cells based on the fill color?
(for example you only want it to count the ones that have the fill
color red. Not count the number of red cells but count the values in the
cells)

Hi,

If you are using Excel 2007, then you can filter based on colour and then
use the SUBTOTAL(9,range) function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mike" wrote in message
...
Can excel count values in a range of cells based on the fill color?
(for example you only want it to count the ones that have the fill
color red. Not count the number of red cells but count the values in the
cells)

It should be


SUBTOTAL(3,range)


or


SUBTOTAL(2,range)


for counting, 9 will sum the values

--


Regards,


Peo Sjoblom


"Ashish Mathur" wrote in message
...
Hi,

If you are using Excel 2007, then you can filter based on colour and then
use the SUBTOTAL(9,range) function.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"mike" wrote in message
...
Can excel count values in a range of cells based on the fill color?
(for example you only want it to count the ones that have the fill
color red. Not count the number of red cells but count the values in the
cells)