Shorten multiple sumifs

[Chad]

Hi

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

Sumif(A2,Sheet1!B2:B10,Sheet1!D210)+Sumif(A2,Sheet1!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

 

[joel]

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 multipl
columns and added a new parameter which is a number indicating th
number of columns to Skip. If this number was on then it would wor
exactly like a regular SumIF.

Here is the code. If calls the reuglar sum If multiple times summin
one column at a time. The only difference between my formula and th
regular Sumif is the compare data (A2) must be a cell Range and not
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

 

[Chad]

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

 

[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,D210,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