COUNTIF color = red?

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Mar 10, 2003

• #2

Not without getting VBA involved

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Mar 10, 2003

• #3

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

 

Upvote 0

J

jimboy

Well-known Member

Joined

Apr 11, 2002

Messages

2,314

• Mar 10, 2003

• #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

 

Upvote 0

L

Lisa928

Board Regular

Joined

Jun 13, 2002

Messages

173

• Mar 10, 2003

• #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...

 

Upvote 0

Iridium

Well-known Member

Joined

Jul 15, 2002

Messages

2,831

• Mar 10, 2003

• #6

Here you go!

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

HTH

 

Upvote 0

L

Lisa928

Board Regular

Joined

Jun 13, 2002

Messages

173

• Mar 10, 2003

• #7

BEAUTIFUL!

Thanks so much!

 

Upvote 0

IsabellaLearning

Board Regular

Joined

May 23, 2009

Messages

115

• May 27, 2009

• #8

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

jimboy said:

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

Click to expand...

 

Upvote 0

L

Legacy 68668

Guest

• May 27, 2009

• #9

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

 

Upvote 0

G

ggbc11

New Member

Joined

Jul 14, 2011

Messages

5

• Jul 14, 2011

• #10

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?

 

Upvote 0