VBA: Creating UDF for Sumifs by colour and price point

[chengmans123]

Hi Everyone,
I've found codes online to help me Sumifs by colour for situations where the column with coloured data is to be summed.
However, i'm looking Sumifs by multiple criteria across different columns. See example below.

I have the following code to countifs by colour and price points
If Price Point = Purple, Price Point = $7.99 then count the number of weeks on $7.99

Function COUNTIFCOLOUR(Colour As Range, UnitPrice As Range, rng As Range) As Long

Dim NoCells As Long
Dim cellColour As Long
Dim rngCell As Range
cellColour = Colour.Interior.Color

For Each rngCell In rng
    If rngCell.Interior.Color = cellColour And rngCell.Value = UnitPrice Then
       NoCells = NoCells + 1
    End If
Next
COUNTIFCOLOUR = NoCells
End Function

I want to create a function where it does something similar but instead of counting it sums up the column "Unit Sales"

ie. If Price Point = Purple, Price Point = $7.99, then sum the column "Unit Sales"

A function that will give me the number "527 Units" in the picture above.

Thank you for taking the time to read this post!

 

[Jeffrey Mahoney]

I have done something similar before. UDFs are slow. It is best to create a hidden helper column that provides the status you need. If you have conditional formatting, you can create a status to give you the same. Then SUMIFS is a true function and calculates much faster.

My $.02