Sum by color with multiple arguments

[Watchdawg]

I've created a module with the assistance of Google, and it works fantastically. What I would now like to do is perform this function only if the cell value in range H2:H1000 is "Consol LTL" AND the color as shown below. Any ideas?

Formula is:

=SumByColor($E$2:$E$1000,T23)

The Module is:

Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function

 

[Jeffrey Mahoney]

This gets the count of cells that match the color you provided and has the cell contents "Consol LTL".

Function SumByColor(SumRange As Range, SumColor As Range) As Long
  Dim SumColorValue As Integer
  Dim TotalSum As Long
  Dim rCell As Range
  
  SumColorValue = SumColor.Interior.ColorIndex
  
  For Each rCell In SumRange
    If rCell.Interior.ColorIndex = SumColorValue And rCell.Value = "Consol LTL" Then
    TotalSum = TotalSum + 1
  End If
  Next rCell
  SumByColor = TotalSum
End Function

 

[Watchdawg]

This gets the count of cells that match the color you provided and has the cell contents "Consol LTL".

Function SumByColor(SumRange As Range, SumColor As Range) As Long
  Dim SumColorValue As Integer
  Dim TotalSum As Long
  Dim rCell As Range
 
  SumColorValue = SumColor.Interior.ColorIndex
 
  For Each rCell In SumRange
    If rCell.Interior.ColorIndex = SumColorValue And rCell.Value = "Consol LTL" Then
    TotalSum = TotalSum + 1
  End If
  Next rCell
  SumByColor = TotalSum
End Function

Thank you, but when the code is put in place, the return is 0 for some reason. The quantities to be summed are in E2:E1000, the label (Consol LTL) is in H2:H1000. The color that it's checking is in T23 and the formula for calculating is in U23. How does that impact the processes. In the attached image, there is one line with Consol LTL (derived from a formula) so the Sum on the right should be returning 5.

 

[Jeffrey Mahoney]

Sorry, Didn't pay attention. Please revise to this:

Function SumByColorAndText(SumRange As Range, SumColor As Range, TxtRng As Range, TxtFltr As String) As Long
  Dim SumColorValue As Integer
  Dim TotalSum As Long
  Dim rCell As Range
  Dim iCel As Range
  
  If TxtRng.Columns.Count > 1 Then Exit Function
  
  SumColorValue = SumColor.Interior.ColorIndex
  
  For Each rCell In SumRange
    Set iCel = Intersect(rCell.EntireRow, TxtRng.EntireColumn)
    If rCell.Interior.ColorIndex = SumColorValue And iCel.Value = TxtFltr Then
      TotalSum = TotalSum + rCell.Value
    End If
  Next rCell
  SumByColorAndText = TotalSum
End Function

Where you have to provide the range of cells that may contain the text you want to filter on and the actual filter text. This matches the filter to the color cell by the same row

OR try this as an alternative

Function iColor(Rng As Range) As Single
  Application.Volatile
  iColor = Rng.Interior.Color
End Function

Where you have to do the work in the cell as a manual formula. This function returns the Interior color and not the Interior ColorIndex. This would more accurately match the very specific color applied to both cells. You would then have to sum the values returned in the column. Use as a formula like this:
=IF(AND(iColor(E10)=iColor($C$7),H10="Consol LTL"),E10,0)
Where E10 is the cell with the value to add if it matched the color and text criteria
Where C7 is the cell color criteria
Where H10 is the cell with the text that must match "Consol LTL"