Hi

Would anyone know of a way to shorten multiple sumifs. This formula
is getting very long.

Sumif(A2,Sheet1!B2:B10,Sheet1!D2:D10)+Sumif(A2,She et1!B2:B10,Sheet1!
F2:F10)+Sumif(A2,Sheet1!B2:B10,Sheet1!G2:G10)etc.

The problem is the design of the sheet where the summed data is not in
adjacent columns. Is there a custom function to shorten this? Thanks
in advance.

Chad

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

Joel

This is the second time you have helped me in as many days. Thanks so
much. I have not tested it out yet but your prior suggestion worked
really well. Thanks again Joel.

Chad

Hi Joel

Your code works nicely for one line of the sumed range. For example
if A2 was the summed range and the letter "A" was in A2 then it will
match "A" in Columns B2:B10,D2:D10,F2:F10 etc. But it gives the sum
for the first row nothing moreSo if A wer were in the first row it
would sum B2,D2,F2 etc but if I appeared anywhere else in the range it
would not sum these instances. Is there something I missed?

Thanks

Chad