Need Summation UDF - (SUMIF)+SUMIF)

That looks pretty straight-forward to me, why do you want to use a UDF for that?

Is there a specific part of this that you need help with?

You say that you are new to VBA, so I might suggest that you start with some introductory materials to VBA and UDF's. I put this tutorial together to illustrate the process I use to build UDFs: https://www.excelforum.com/tips-and-...uild-udfs.html

Once you are comfortable with the basic ideas and mechanics behind creating UDFs, then you will need to become more familiar with the specific statements and programming structure that will be needed for this task. I am not sure what programming structure you will want to use, but some ideas that I expect will become important:
Loops: http://www.wiseowl.co.uk/blog/s193/vba-loops.htm
testing conditions (such as whether to include a value in the summation): https://powerspreadsheets.com/if-then-else-statement/
or maybe simply call the SUMIFS() function within VBA: http://www.cpearson.com/excel/Callin...ionsInVBA.aspx

That should get you started. Let us know where you get stuck.

Hi MrShorty,

After some trial and errors have come with UDF ( honestly lifted some part of the code ). Below is the function which is giving me equivalent result to SUMIF formula combination.

Is there better method to what i have below, currently way i think .. user will select two criteria columns , corresponding criteria and two sum columns to arrive at the answer.

Please guide as am having problem with

1. UDF works fine when sum ranges and criteria ranges and criteria is in the same sheet but

2. When sum range and criteria range is different sheet it gives #VALUE!

SUMID(Mas[Like],[@L],Mas[Rank],Mas[act],[@I],Mas[Rank2])


UDF worked ..........

Function SUMID(CritRan As Range, crit As Variant, celSum1 As Range, Crit2ran As Range, crit2 As Variant, celSum2 As Range) As Double



Dim rgCrit As Range, rgSum As Range

Dim rgCrit2 As Range, rgSum2 As Range

Dim Cel As Range

Set Cel = Application.Caller

Set rgCrit = Range(CritRan, Intersect(CritRan.EntireColumn, Cel.EntireRow))

Set rgSum = Range(celSum1, Intersect(celSum1.EntireColumn, Cel.EntireRow))

Set rgCrit2 = Range(Crit2ran, Intersect(Crit2ran.EntireColumn, Cel.EntireRow))

Set rgSum2 = Range(celSum2, Intersect(celSum2.EntireColumn, Cel.EntireRow))

SUMID = Application.SumIf(rgCrit, crit, rgSum) ^ 3 + Application.SumIf(rgCrit2, crit2, rgSum2)


End Function

Aplogies for the delay, I somehow missed your response from a couple of days ago.

1. UDF works fine when sum ranges and criteria ranges and criteria is in the same sheet but
2. When sum range and criteria range is different sheet it gives #VALUE!

This is usually bad range definitions, and, sure enough, your function contains "unqualified" ranges. For example, you code includes

Please Login or Register  to view this content.

Note how I added in italics the ActiveSheet to emphasize that, whenever you fail to qualify a Range reference, Excel is going to assume you mean ActiveSheet.Range(...). This can be especially problematic with UDF's because your code cannot control what sheet is active at the time of execution. An important rule with UDF's (and, IMO, in code in general), never use unqualified Range references. Find some way to explicitly tell VBA where the desired Range is located.

I think there might be a better way to do those Set Rgcrit=... statements, but I must admit that those structured table references throw me off, because never use structured tables or structured table references. If those were normal A1 references, I could be more help (or if you uploaded a sample file, so I could convert those to regular A1 references).

As explained in the Intersect method's help file (https://msdn.microsoft.com/VBA/Excel...t-method-excel ), "If one or more ranges from a different worksheet are specified, an error will be returned." If you intend to use ranges from different worksheets in this UDF, then I don't think the Intersect method is going to be usable in this UDF.

What are you trying to do with the Intersect method? If I copy and adjust so that all the data is on one sheet (and insert a stop statement at the beginning as I explained in my tutorial so I can access VBA's watch and locals window), it appears that the result of the Intersect method is to merely create a copy of the input ranges: rgcrit points to the same range as critran for example. This suggests to me that the Intersect method is either unnecessary to the algorithm, or one of us is not understanding the purpose of the Intersect method.

I kind of would have expected a simple "one statement" UDF something like this (leaving your old code commented out and including the Stop statement):

Please Login or Register  to view this content.

This returns the same result as your FORMULA column indicates when all references are from the same sheet, and works just fine when inputs are from different sheets.

I note that there seems to be an error in your referencing between the FORMULA column and the UDF column. Your FORMULA column uses a relative reference for the final argument of the second SUMIF() function, yielding the results of 2, 11, and 27. Your UDF column has an absolute reference for the final reference of the second Sumif() function, which yields 2, 10, and 30. I do not know which result is correct, as I cannot tell if you intended for this reference to be relative or absolute. I suggest a couple of moments looking at that to see which is intended, and adjust the desired reference accordingly.