# Help with UDF: Sum the results of a calculation across a pair of cells down entire ranges

1. ## Help with UDF: Sum the results of a calculation across a pair of cells down entire ranges

I am very new to VBA (this is the first thing I've ever built using it), so I apologize if my terminology is off.
I tried my hand at building a small handful of UDFs to simplify a somewhat annoying task that some of my colleagues and I regularly have to complete. Each of these UDFs essentially takes a multiple-if statement and simplifies it so that instead of having to type...

...someone could just type out...

The UDFs that I created seem to be working fine. However, I want to be able to take the sum of NEWFUNCTION for a range by entering something like
I am having trouble figuring out how to code this in VBA properly, and was hoping for some advice. The code for one of my UDFs is below....

To explain further, here is an example screenshot

This is just a simplified example, but there can be tens of thousands (or more) rows.

Currently, the formula populating C2 is
which is dragged down to C20. It is basically saying, if A is less than B, then A, otherwise B. But there are some intricacies with the way that negative numbers and zero values are treated that are not apparent in the screenshot, which is why the formula appears unnecessarily lengthy.
The formula in D2 and dragged to D20 is
....for which the VBA code is above. You can see in the example screenshot that it ends up with the same result as the pasted if/then formula in C2 and (in row 21) they sum up to the same amount.
The value in F2 (which is the same as either SUM(C2:C20) or SUM(D2:D20), is what I would like to be achievable by entering something like
I am basically trying to remove a step in the process where my colleagues and I would have to type
in Column C, drop it down, and sum it.

2. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

Try this - hope it's what you are after...

3. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

here is my attempt

4. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

mgs

and
combined, will give you #value error if there is any zero's in first range

5. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

mgs

and
combined, will give you #value error if there is any zero's in first range

6. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

my previous code seems to fail if the range is only 1 cell
so i have added more code to counter it

7. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

Formula:

Formula:

there is no minus in the second formula at all

the 2nd code does not match what you wrote in the "code" format

is the first or the second one correct?

8. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

My fault. The 1st code is correct. I accidentally copied the formula out of the wrong cell when I posted here. Thanks for catching. Will update.

9. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

in that case no amendment of the UDF is required

does the UDF supplied do the job you want?

10. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

Yeah! It appears to be working fine. And the code is the same as what you posted above?

I have a few other functions that are somewhat similar in structure (nested if/then statements) that i am going to try to apply the same treatment to using that as a model. Thanks!

11. ## Re: Help with UDF: Sum the results of a calculation across a pair of cells down entire ran

you can certainly apply the same framework for different UDF

if your nested if/then gets more complicated you might want to investigate in using SELECT case instead (which is a benefit of using UDF which you cannot use with just normal formulas)
http://www.techonthenet.com/excel/formulas/case.php

There are currently 1 users browsing this thread. (0 members and 1 guests)