Thanks Bernie. That was a very elegant solution. Much appreciated.

Ben



--



"Bernie Deitrick" wrote:

Ben,

It's not a recursive call. To assign a value to a function (the value that
it returns) you assign a value using

FunctionName = Value
mySumIf = Value

Since it is stepping through the areas of the range that is passed to it,
and doing the SUMIF piece-meal, we need to remember the old value, and add
any new value to it:

mySumIf = mySumIf + Value

The first time through, mySumIf has a value of 0, then it is assigned the
value of the first area of the range being passed, then increased by the
second area.....


So, when you use

mySumIf((C1,E1:H1,J1:K1,M1:O1),"0")

it is the same as using

SUMIF(C1,"0") + SUMIF(E1:H1,"0") + .... + SUMIF(M1:O1,"0")

If you want to see how it does it in action, put a break into the function,
and then press Ctrl-Alt-F9 from within Excel, then step through the
function.

HTH,
Bernie
MS Excel MVP




"Ben" wrote in message
...
Bernie,

I do wanted to ask if you can give further explanation as to why it works.
Not quite clear on the recursive call. Thanks again.

Ben


--



"Bernie Deitrick" wrote:

Ben,

Copy the code below into a module of the workbook where you want the
formula, and use it like this
in your sheet:

=mySumIf((C1,E1:H1,J1:K1,M1:O1),"0")

where the separate ranges are listed inside parens, with commas between.
You can copy down to match
the range that you want.

HTH,
Bernie
MS Excel MVP


Function mySumIf(inRange As Range, inArg As String) As Double
Dim myArea As Range
For Each myArea In inRange.Areas
mySumIf = mySumIf + Application.WorksheetFunction.SumIf(myArea, inArg)
Next myArea
End Function


"Ben" wrote in message
...
Hi all,

I am wondering if there's a way to use SUMIF(range, criteria) if the
range
is not a contiguous, ie, the data is on the same row but in
non-contiguous
columns. I am looking to use it in my code to replace a very long
formula.
But I tried to use it in the formula, where the range is not
contiguous. But
that didn't work. Is there any way to make it work with non contiguous
ranges? If there is, I would also like to copy down this column with
the
same formula to work on the corresponding row. Thanks for sharing your
thought.

Ben



--