marcidee

Board Regular
Joined
May 23, 2016
Messages
162
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
 

Some videos you may like

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
Joined
Apr 4, 2009
Messages
3,680
Hello,

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

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

marcidee

Board Regular
Joined
May 23, 2016
Messages
162
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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
    ' 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

Board Regular
Joined
May 23, 2016
Messages
162

ADVERTISEMENT

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

Board Regular
Joined
May 23, 2016
Messages
162
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
Joined
Apr 4, 2009
Messages
3,680

ADVERTISEMENT

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()
 

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Your whole code contains SIX Functions ...!!!

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

i.e the Function SumCellsByFontColor()
 

marcidee

Board Regular
Joined
May 23, 2016
Messages
162
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)
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top