# COUNTIF color = red?

This is a discussion on COUNTIF color = red? within the Excel Questions forums, part of the Question Forums category; I want to use a sumif and/or countif function in a spreadsheet, but I want it to associate with the ...

1. ## COUNTIF color = red?

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?

2. Not without getting VBA involved

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

4. 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

5. 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...

6. BEAUTIFUL!

Thanks so much!

7. ## Re: COUNTIF color = red?

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

Originally Posted by 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

8. ## Re: COUNTIF color = red?

=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.
Code:
```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.

9. ## Re: COUNTIF color = red?

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?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•