SUMPRODUCT and SUMIFS combined with Sumifbycolor (VBA code)

[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!

 

[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