1

I'm using the below formula to calculate a sum. The formula only calculates the sum of those values which has the string "Yes" written in the same row under the column "Paid?". I have also written my own UDF that searches a given range for cells with a given background colour and returns a range containing only those cells with that specified background colour.

=SUMIF(SelectRangeByColour(O5;M5:M35);"Yes";SelectRangeByColour(O5;I5:I35))

(In case you are confused about the semicolons, that's the standard list separator used by Excel since my computer's language is Swedish. I have tried changing it to a comma, but that didn't help)

The problem is that in this specific case, the first "SelectRangeByColour" function returns a range that looks like this: $M$5:$M$7;$M$15, and the second one returns this rannge: $I$5:$I$7;$I$15. However, since the semi-colon is used both to separate the values in the range, but also to separate the different arugments in the function SUMIF, the function ends up recieving too many arguments and ouputs a #VALUE! error.

This is what the SUMIF function ends up trying to parse:

=SUMIF($M$5:$M$7;$M$15;"Yes";$I$5:$I$7;$I$15)

And therefore it sees $M$5:$M$7 as the main area, $M$15 as the condition, $I$5:$I$7 as the optional sum area, and finally $I$15 as an extra argument that doesn't exist.

I want the SUMIF function however to parse $M$5:$M$7;$M$15 as the main area, "Yes" as the condition, and finally $I$5:$I$7;$I$15 as the sum area. How could I achieve this?

Below is the code from the UDF "SelectRangeByColour" in case that could be helpful in any way:

Function SelectRangeByColour(colourFilter As Range, rangeToSearch As Range) As Range
    ' Get the colour to filter by
    colour = colourFilter.Interior.Color
    
    ' Initialize the finalRange to return from the function
    Dim finalRange As Range
    
    For Each cell In rangeToSearch
        If cell.Interior.Color = colour Then
            ' If the finalRange is not empty, then add the current cell to it
            If Not finalRange Is Nothing Then
                Set finalRange = Union(finalRange, cell)
            Else
                ' If final range is empty, set it equal to the current cell
                Set finalRange = cell
            End If
        End If
    Next
    
    ' In case nothing matches the given colour, set the range to the colour
    ' as not to display a #VALUE! error
    If finalRange Is Nothing Then
        Set finalRange = colourFilter
    End If
    
    Set SelectRangeByColour = finalRange
    
End Function
Jinado
  • 21
  • 3
  • 2
    That's not actually the issue. SUMIF (and similar functions) just don't work on discontiguous ranges. – Rory Mar 31 '21 at 11:28
  • 1
    A VBA wrapper around your calls to application.worksheetfunction.SUMIF() that splits the return from SelectRangeByColour by the ";" and makes (in this case) two submissions to SUMIF, then returns the total of those submissions? As Rory says, the basic function will never do what you need it to. – Amiga500 Mar 31 '21 at 11:36
  • Alright, thank you. That makes sense. I think I understand how to solve my problem. – Jinado Mar 31 '21 at 13:13

0 Answers0