# Sum If question

#### fbacchus

##### Board Regular
Hi:

Can anyone send me the code required to check a range of cells (with numeric data) and if the data is "red" in color, sum them (only the reds) all. The workbook contains multiple worksheets and this can be the case for any of the worksheets.

frank

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Haven't checked out the VBAX link, but here's another one you may want to look into.

Just scroll down a bit till you find "Summing The Values Of Cells With A Specific Color"
http://www.cpearson.com/excel/colors.htm

Hope it helps,
Dan

[b]Sum If Question (Color numbers in Cell)[/b]

Thanks for the information. Dan, I checked the URL you gave me and it looks like what I an looking for under "Summing The Values Of Cells With A Specific Color".

I tried the code and it works well if "background color (fill)" is used. When I make the numbers "colored (e.g. RED)" it returns 0. How can I tell the code to look at the foreground (the color of the numbers).

I am a beginner with VBA so I and not clear how it works. I looks like "OfText indicates whether to return the ColorIndex of the Font (if True)" but I am not sure how to set "OfText".

frank

Hi Frank, you're on the right track.
If you change the formula being used,
(example given is) =SUMBYCOLOR(A1:A10,3,FALSE)
to
=SUMBYCOLOR(A1:A10,3,TRUE)
it should sum up the values in the range A1:A10 with red font.
(The 3 in the formula refers to the color red. You would use 10 instead of 3 to sum up the values with green font, 6 for yellow font, etc.)

Hope it helps,
Dan

On looking at this again, maybe a little more complete reply might be more helpful. Here's a way to handle the whole thing.
Assumes your range is A1:A25, and the color to sum up is still red, and you're only concerned with the font color, not the background color and you want the summed values to appear in B26.

You can replace the function code with this:
Code:
``````Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = True) As Double
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
OK = (Rng.Font.ColorIndex = 3)
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
End Function``````
Then in a standard module (the same one the function is in if you like) you can have this code.
Code:
``````Sub SumTheRedOnes()
[B26].Formula = "=SUMBYCOLOR(A1:A25,3,TRUE)"
End Sub``````
Now you can simply plant a button on the sheet to run the SumTheRedOnes code whenever there are any changes made to your range A1:A25.

And a more simple approach altogether might be to replace all of the above with this:
Code:
``````Sub SumRedOnly()
Dim Rng As Range
Set Rng = Range("A1:A25")
For Each cell In Rng
If IsNumeric(cell.Value) And cell.Font.ColorIndex = 3 Then i = i + cell.Value
Next cell
[B26] = i
End Sub``````

Does this help a little more?
Dan

Dan,

TRhnaks a great deal. I understand the code below and it look to be the simplest. One last question "What does [B26} represent ? Is that the designated cell where the results will show ?

thanks

frank

code:
--------------------------------------------------------------------------------
Sub SumRedOnly()
Dim Rng As Range
Set Rng = Range("A1:A25")
For Each cell In Rng
If IsNumeric(cell.Value) And cell.Font.ColorIndex = 3 Then i = i + cell.Value
Next cell
[B26] = i
End Sub

Exactly. [B26] (or otherwise written as) Range("B26") is where the sum of the red values will be put. You can replace that with any range you choose.

I agree this code can be more intuitive to read, but bear in mind that by using it you'll be sacrificing some speed so if you have a really huge range you're performing this on you'll likely notice the difference in the time it takes to execute. (But... as I see it, the slowest macro you can understand & make work is still faster than a quicker one you can't! )

Hope it helps,
Dan

Replies
10
Views
284
Replies
1
Views
336
Replies
2
Views
351
Replies
11
Views
414
Replies
7
Views
481

1,203,514
Messages
6,055,838
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back