Sum by color with multiple arguments

Watchdawg

Board Regular
Joined
Jan 21, 2015
Messages
84
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:
Excel Formula:
=SumByColor($E$2:$E$1000,T23)

The Module is:
VBA Code:
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
This gets the count of cells that match the color you provided and has the cell contents "Consol LTL".


VBA Code:
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
 
Upvote 0
This gets the count of cells that match the color you provided and has the cell contents "Consol LTL".


VBA Code:
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.
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    64.8 KB · Views: 21
Upvote 0
Sorry, Didn't pay attention. Please revise to this:
VBA Code:
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
VBA Code:
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"
 
Upvote 0
Solution

Forum statistics

Threads
1,223,954
Messages
6,175,603
Members
452,658
Latest member
GStorm

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top