How do I use the SumIF function with a criteria of a cell color?
 

Hi,
I am trying to use the SumIF function and use a criteria based on the color
of the cells. I see I can sort and filter based on cell color, but cannot
find how to add based on the cell color. Is this available, or do I need to
wait for Microsoft to create a =color() function?

How do I use the SumIF function with a criteria of a cell color?
 

Are the cells just randomly colored? Or are they conditionally formatted to
be a specific color based on certain conditions. If the latter, then you
could do a SUMIF based on the same conditions.
--
John C


"Infernoo1" wrote:

Hi,
I am trying to use the SumIF function and use a criteria based on the color
of the cells. I see I can sort and filter based on cell color, but cannot
find how to add based on the cell color. Is this available, or do I need to
wait for Microsoft to create a =color() function?

How do I use the SumIF function with a criteria of a cell color?
 

Microsoft are not going to create a Volour fuynction, so write your own.

Function SumByColour(rng As Range, ci)
Dim cnt As Long
Dim cell As Range

Application.Volatile

For Each cell In rng

If cell.Interior.ColorIndex = ci Then

cnt = cnt + 1
End If
Next cell
SumByColour = cnt
End Function


and in you worksheet

=SumByColour(A1:A10,3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Infernoo1" wrote in message
...
Hi,
I am trying to use the SumIF function and use a criteria based on the
color
of the cells. I see I can sort and filter based on cell color, but cannot
find how to add based on the cell color. Is this available, or do I need
to
wait for Microsoft to create a =color() function?

How do I use the SumIF function with a criteria of a cell colo
 

I think this will give you what you need:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim rng As Range
Application.Volatile True

For Each rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(rng.Interior.ColorIndex = WhatColorIndex)
End If
Next rng

End Function


Regards,
Ryan---


--
RyGuy


"Bob Phillips" wrote:

Microsoft are not going to create a Volour fuynction, so write your own.

Function SumByColour(rng As Range, ci)
Dim cnt As Long
Dim cell As Range

Application.Volatile

For Each cell In rng

If cell.Interior.ColorIndex = ci Then

cnt = cnt + 1
End If
Next cell
SumByColour = cnt
End Function


and in you worksheet

=SumByColour(A1:A10,3)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Infernoo1" wrote in message
...
Hi,
I am trying to use the SumIF function and use a criteria based on the
color
of the cells. I see I can sort and filter based on cell color, but cannot
find how to add based on the cell color. Is this available, or do I need
to
wait for Microsoft to create a =color() function?