Bernie,

It worked. Thank you so much for that great suggestion!

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



--