M michelle Sep 3, 2008 #1 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?
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?
B Bob Phillips Sep 3, 2008 #2 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))
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))
G Gord Dibben Sep 3, 2008 #3 How did the font color get to be red? If by Conditional formatting, Excel won't pick up the colorindex of the font. See Chip Pearson's site if that is the case. http://www.cpearson.com/excel/CFColors.htm Gord Dibben MS Excel MVP
How did the font color get to be red? If by Conditional formatting, Excel won't pick up the colorindex of the font. See Chip Pearson's site if that is the case. http://www.cpearson.com/excel/CFColors.htm Gord Dibben MS Excel MVP
