More Functions based on Font Color

  • Thread starter Thread starter michelle
  • Start date Start date
M

michelle

I'm trying to add the cells who's font color is red. What I have so far is

=SUMIF(F4:F116," ")

Am I close?
 
No. You need a UDF as well as a formula

Add this function

Function CellColours(rng As Range) As Variant
Dim ary As Variant
Dim cell As Range
Dim i As Long

ReDim ary(1 To rng.Rows.Count)
i = 1
For Each cell In rng

ary(i) = cell.Font.ColorIndex
i = i + 1
Next cell
CellColours = ary
End Function

and then sum like this to sum all red font cells

=SUMPRODUCT(--(CellColours(A1:A10)=3) ,A1:A10)

and simply count all green font cells like

=SUMPRODUCT(--(CellColours(A1:A10)=10))
 
Back
Top