Shorten multiple sumifs
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 |
Shorten multiple sumifs
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 |
Shorten multiple sumifs
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 |
Shorten multiple sumifs
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 |
All times are GMT +1. The time now is 03:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com