Question

[Tom]

Is there a way to use the sumif function in regards to a
cell's format? For example, I want to sum all cells that
are filled in red.

 

[Norman Harker]

Hi Tom!

Try a visit to Chip Pearson's site for this issue:

Chip Pearson:
http://www.cpearson.com/excel/colors.htm

Here's an extract that covers what you want:

Start Extract>>
The following function will return the sum of cells in a range that
have either an Interior (background) or Font of a specified color.
InRange is the range of cells to examine, WhatColorIndex is the
ColorIndex value to count, and OfText indicates whether to return the
ColorIndex of the Font (if True) or the Interior (if False).

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE)

<<End Extract

You'll find that bookmarking Chip's Index page is well worth it.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Gord Dibben]

Tom

If the cells are colored due to Conditional Formatting, use the same
conditions to SUMIF.

If manually colored you must use code.

See Chip Pearson's site.

http://www.cpearson.com/excel/colors.htm

Scroll down to "Summing by colors"

Gord Dibben Excel MVP

 

[Tom]

Thanks for the help. I am unable to call the function
however in my excel worksheet. I created the function in
the visual basic editor but receive an error "#NAME?" when
I try to call it. Any suggestions?