Sumifs with UDF for Sum by Text Color

[Agame21]

Hello,

This site has been incredibly helpful for me in the past by searching for similar requests by other posters but I could not find an answer to this problem I am facing...

I have been using a UDF I found online to do a sumif based on the text color of the sum range. See below:

Public Function SumByColor(pRange1 As Range, pRange2 As Range) As Double
Application.Volatile
Dim rng As Range
Dim xTotal As Double
xTotal = 0
For Each rng In pRange1
If rng.Font.Color = pRange2.Font.Color Then
xTotal = xTotal + rng.Value
End If
Next
SumByColor = xTotal
End Function

I am now trying to figure out how to combine this with another criteria to only sum if the text is red AND it is Type 1.

Excel 2010
	B	C	D	E	F	G
5	Type	Cost			Total Red	250
6	1	$ 20			Red Type1	??
7	1	$ 50
8	2	$ 60
9	1	$ 50
10	1	$ 80
11	3	$ 90
12	2	$ 20
Sheet1

Is it possible to combine a UDF with a SumIfs? Do I need another UDF that does both of these? Please help!

 

[dreid1011]

You might be able to add to the IF statement like this:

If rng.Font.Color = pRange2.Font.Color And rng.Offset(0,-1).Value = 1 Then

 

[Agame21]

You might be able to add to the IF statement like this:

If rng.Font.Color = pRange2.Font.Color And rng.Offset(0,-1).Value = 1 Then

dreid1011,

Thank you very much for the help! This works great for the example I provided. However, I will actually need to do this for more than just one column over.

Excel 2010
	B	C	D	E
5	Type	Cost A	Cost B	Cost C
6	1	$ 20	$ 30	$ 20
7	1	$ 50	$ 40	$ 40
8	2	$ 60	$ 40	$ 60
9	1	$ 50	$ 50	$ 50
10	1	$ 80	$ 100	$ 90
11	3	$ 90	$ 80	$ 90
12	2	$ 20	$ 20	$ 30
13
14	Total Red	250	110	170
15	Red Type 1
16	Red Type 2
17	Red Type 3
Sheet1

Rather than offsetting by 1 column every time, is there a way to make it always look to Column B?

Thanks again!

 

[dreid1011]

Try this:

And Range("B"& rng.Row).Value = 1

 

[Agame21]

Try this:

And Range("B"& rng.Row).Value = 1

Thank you sooo much!!! That worked perfectly

 

[dreid1011]

You're welcome. Thanks for the feedback.