# Formula Help

#### marcidee

##### Board Regular
Can someone help me 'tweak' this formula to only 'sum' if it sees a '3' in column AP? =SumCellsByColor(B27:AL27, \$AV\$4)

Your help would be very much appreciated

Marc

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### James006

##### Well-known Member
Hello,

you should post the code ( Alt + F11 ) with your next message ...

#### marcidee

##### Board Regular
Hello,

you should post the code ( Alt + F11 ) with your next message ...

This is the code:

Function GetCellColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
Next
Next
GetCellColor = arResults
Else
GetCellColor = xlRange.Interior.Color
End If
End Function

Function GetCellFontColor(xlRange As Range)
Dim indRow, indColumn As Long
Dim arResults()

Application.Volatile

If xlRange Is Nothing Then
Set xlRange = Application.ThisCell
End If

If xlRange.Count > 1 Then
ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
For indRow = 1 To xlRange.Rows.Count
For indColumn = 1 To xlRange.Columns.Count
arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
Next
Next
GetCellFontColor = arResults
Else
GetCellFontColor = xlRange.Font.Color
End If

End Function

Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByColor = cntRes
End Function

Function SumCellsByColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Interior.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Interior.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByColor = sumRes
End Function

Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
Dim indRefColor As Long
Dim cellCurrent As Range
Dim cntRes As Long

Application.Volatile
cntRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
cntRes = cntRes + 1
End If
Next cellCurrent

CountCellsByFontColor = cntRes
End Function

Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Cells(1, 1).Font.Color
For Each cellCurrent In rData
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function

#### James006

##### Well-known Member
Hello,

Below is your modified UDF ...

Code:
``````Function SumCellsByFontColor(rData As Range, cellRefColor As Range, excepR As Range)
' only 'Sum' if there is a '3' in column AP
' Below is example how to use this Formula
' =SumCellsByFontColor(B27:AL27, \$AV\$4,AP27)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Font.Color
For Each cellCurrent In rData
If excepR.Value = 3 Then
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function``````

Hope this will help

#### marcidee

##### Board Regular

Hello,

Below is your modified UDF ...

Code:
``````Function SumCellsByFontColor(rData As Range, cellRefColor As Range, excepR As Range)
' only 'Sum' if there is a '3' in column AP
' Below is example how to use this Formula
' =SumCellsByFontColor(B27:AL27, \$AV\$4,AP27)
Dim indRefColor As Long
Dim cellCurrent As Range
Dim sumRes

Application.Volatile
sumRes = 0
indRefColor = cellRefColor.Font.Color
For Each cellCurrent In rData
If excepR.Value = 3 Then
If indRefColor = cellCurrent.Font.Color Then
sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
End If
End If
Next cellCurrent

SumCellsByFontColor = sumRes
End Function``````

Hope this will help

Thank you very much for this - please can you tell me -does this replace all teh script or just pat of the script?

#### marcidee

##### Board Regular
I have replaced the last part of the code with yours - however I am still getting the same results - can you advise?

#### James006

##### Well-known Member

Thank you very much for this - please can you tell me -does this replace all teh script or just pat of the script?

The complete UDF has to be replaced by this revised version ... i.e the Function SumCellsByFontColor()

#### marcidee

##### Board Regular
If I replace the whole code with yours, I get #NAME ?

#### James006

##### Well-known Member
Your whole code contains SIX Functions ...!!!

ONLY the very LAST Function needs to be replaced ...

i.e the Function SumCellsByFontColor()

#### marcidee

##### Board Regular
Your whole code contains SIX Functions ...!!!

ONLY the very LAST Function needs to be replaced ...

i.e the Function SumCellsByFontColor()

Thank you for your patience - I have tried that - I do not get any error messages - however it still adding in the coloured cells that have a 1 or 2 in column AP (results are in AQ if that is relevant, coloured cells are B to AL)

formula in AP is =CountCellsByColor(B4:AL4, \$AV\$4)
formual in AQ is =SumCellsByColor(B4:AL4, \$AV\$4)

Replies
0
Views
88
Replies
3
Views
47
Replies
0
Views
78
Replies
6
Views
51
Replies
0
Views
55

1,109,445
Messages
5,528,801
Members
409,835
Latest member
Mafu1267

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...