I made a custom UDF.

=SumifSkip(A2,Sheet1!B2:B10,Sheet1!D2:G10,2)


I took your original formula and change the Sum range to be multiple
columns and added a new parameter which is a number indicating the
number of columns to Skip. If this number was on then it would work
exactly like a regular SumIF.

Here is the code. If calls the reuglar sum If multiple times summing
one column at a time. The only difference between my formula and the
regular Sumif is the compare data (A2) must be a cell Range and not a
String.

Function SumifSkip(Comparedata As Range, _
Comparetarget As Range, Sumtarget As Range, _
ColumnSkip As Long)

Numberofcolumns = Sumtarget.Columns.Count
NumberOfrows = Sumtarget.Rows.Count

ComparedataAddr = Comparedata.Address(external:=True)

ComparetargetAddr = Comparetarget.Address(external:=True)

Set ColumnRange = Sumtarget.Resize(NumberOfrows, 1)

SumifSkip = 0
For Count = 0 To (Numberofcolumns - 1) Step ColumnSkip
ColumnRangeAddr = ColumnRange.Address(external:=True)

SumifSkip = SumifSkip + _
Evaluate("Sumif(" & _
ComparedataAddr & "," & _
ComparetargetAddr & "," & _
ColumnRangeAddr & ")")
Set ColumnRange = ColumnRange.Offset(0, ColumnSkip)
Next Count

End Function


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=183258

Microsoft Office Help