Formula Help

[marcidee]

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

 

[James006]

Hello,

In order to add a condition to your UDF ...

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

 

[marcidee]

Hello,

In order to add a condition to your UDF ...

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]

Hello,

Below is your modified UDF ...

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
    ' Condition Added ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    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]

Hello,

Below is your modified UDF ...

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
    ' Condition Added ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    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]

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

 

[James006]

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]

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

 

[James006]

Your whole code contains SIX Functions ...!!!

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

i.e the Function SumCellsByFontColor()

 

[marcidee]

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)