# how to add excel calc to this function

#### rcr1991

##### New Member
A memer turned me on to this fxn, which works perfectly to sum values in cells with a certain internal color.

My problem is I cannot figure out how to use the stdev (standard deviation) function on the same range that is used for the sum. Suggestions?

For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng
(Source: http://www.cpearson.com/excel/colors.htm)

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Andrew Poulsom

##### MrExcel MVP
Try:

Code:
``````Function SDByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
Dim Cell As Range
Dim OK As Boolean
Dim i As Long
Dim Rng As Range
Application.Volatile True
i = 1
For Each Cell In InRange.Cells
If OfText = True Then
OK = (Cell.Font.ColorIndex = WhatColorIndex)
Else
OK = (Cell.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Cell.Value) Then
If i = 1 Then
Set Rng = Cell
Else
Set Rng = Union(Rng, Cell)
End If
i = i + 1
End If
Next Cell
If Not Rng Is Nothing Then
SDByColor = WorksheetFunction.StDev(Rng)
End If
End Function``````

Replies
0
Views
200
Replies
0
Views
893
Replies
4
Views
2K
Replies
13
Views
1K
Replies
0
Views
1K

1,195,945
Messages
6,012,445
Members
441,701
Latest member
vnkendijs

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