COUNTIF color = red?

[Lisa928]

I want to use a sumif and/or countif function in a spreadsheet, but I want it to associate with the color of the text. For example, countif text is red (not ="red" but red in color). I know I can do this with code in VBA, but looking to keep it simple for another user. Can excel differentiate between text color in functions?

Thanks in advance!

 

[Juan Pablo González]

Not without getting VBA involved

 

[just_jon]

I'm not aware of any non-VBA solution, either macro or UDF.

 

[jimboy]

If you paste this into a module, you can use the formula

=SumColor(A1,A2:A10)

(A1 is the colour of cell you want to add)

Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell

SumColor = vResult
End Function

 

[Lisa928]

A user-defined function would probably work. The one above will not work as it is looking at the interior rather than font color. I also will need to tweak to COUNT rather than SUM. Can I change interior to FONT and have it work? Trying now...

 

[Iridium]

Here you go!

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

HTH

 

[Lisa928]

BEAUTIFUL!

Thanks so much!

 

[IsabellaLearning]

JimBoy(or anyone listening

the code you attached, can it be used with 2 colors eg: on the smae row i have blue for sic days and red for annual leave days - can i have two cells to the right one counting sick days only and the other annual leave only for the same line?

regards
Isabella

If you paste this into a module, you can use the formula

=SumColor(A1,A2:A10)

(A1 is the colour of cell you want to add)

Function SumColor(rColor As Range, rSumRange As Range)

Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
End If
Next rCell

SumColor = vResult
End Function

 

[Legacy 68668]

=SumIfColours(A1:C100,3,A1,C1)
where
3 = ColRef to be summed within a range, (3rd column)
A1,C1 have cell color to be summed.

Function SumIfColours(rng As Range, ref As Long, _
                ref1 As Range, ref2 As Range) As Double
Dim r As Range, col1 As Long, col2 As Long
col1 = ref1.Interior.ColorIndex
col2 = ref2.Interior.ColorIndex
ref = ref - 1
Application.Volatile
For Each r In rng.Col(1).Cells
    If (r.Interior.ColorIndex = col1) + (r.Interior.ColorIndex = col2) Then
        SumIfColours = SumIfColours + r.Offset(, ref).Value
    End If
Next
End Function

Note: If you change the colour in the cell, you need to hit F9 to update the result.

 

[ggbc11]

Hi, I'm a bit new to VB so I might be doing something really stupid but would appreciate any help. I've gone to http://www.cpearson.com/excel/colors.htm and copied + pasted the =SumColor() function into VB editor in excel. I've then set up some dumby numbers in a column and randomly coloured some of them red. When I then use the syntax for the function in the example below the code on cpearson's page I just get a "#NAME" error.

Can anyone point me in the right direction?