# SUMPRODUCT and SUMIFS combined with Sumifbycolor (VBA code)

T

#### tessamkiefer

I have a large spreadsheet of consultants' hours by week that also includes their rate, respective supplier and cost center. I color code the spreadsheet by hours entered (red), hours approved (yellow), and hours invoiced (green).

I need to find a way to pull red and yellow hours (uninvoiced) then organize by supplier and cost center. Hoping to combine this with a sumproduct to multiple individual hours times individual rates and do all in one step.

**********I have this for everything except colors:**********

=SUMPRODUCT(--('2014 Hours'!\$H\$2:\$H\$229="8304O")*('2014 Hours'!\$E\$2:\$E\$229="Butler"),'2014 Hours'!\$D\$2:\$D\$229,'2014 Hours'!I\$2:I\$229)

**********And this VBA code for the colors:**********

Function SumIfByColor(InputRange As Range) As Double

Dim clr As Range
Dim ColorSum As Long
Dim ColorIndex As Integer

Approved = 6 'Yellow Background
Entered = 3 'Red Background

ColorSum = 0
On Error Resume Next ' ignore cells without values

For Each clr In InputRange.Cells
If clr.Interior.ColorIndex = Approved Or clr.Interior.ColorIndex = Entered Then
ColorSum = ColorSum + clr.Value
End If
Next clr

On Error GoTo 0

Set clr = Nothing
SumIfByColor = ColorSum

End Function

Hoping for help combining the two, or accomplishing the same end result another way. Thanks!

Z

#### Zaidy036

I have a large spreadsheet of consultants' hours by week that also includes their rate, respective supplier and cost center. I color code the spreadsheet by hours entered (red), hours approved (yellow), and hours invoiced (green).

I need to find a way to pull red and yellow hours (uninvoiced) then organize by supplier and cost center. Hoping to combine this with a sumproduct to multiple individual hours times individual rates and do all in one step.

**********I have this for everything except colors:**********

=SUMPRODUCT(--('2014 Hours'!\$H\$2:\$H\$229="8304O")*('2014 Hours'!\$E\$2:\$E\$229="Butler"),'2014 Hours'!\$D\$2:\$D\$229,'2014 Hours'!I\$2:I\$229)

**********And this VBA code for the colors:**********

Function SumIfByColor(InputRange As Range) As Double

Dim clr As Range
Dim ColorSum As Long
Dim ColorIndex As Integer

Approved = 6 'Yellow Background
Entered = 3 'Red Background

ColorSum = 0
On Error Resume Next ' ignore cells without values

For Each clr In InputRange.Cells
If clr.Interior.ColorIndex = Approved Or clr.Interior.ColorIndex = Entered Then
ColorSum = ColorSum + clr.Value
End If
Next clr

On Error GoTo 0

Set clr = Nothing
SumIfByColor = ColorSum

End Function

Hoping for help combining the two, or accomplishing the same end result another way. Thanks!
ASAP Utilities should help