# Thread: Custom Function (2002)

1. ## Custom Function (2002)

One of my custom functions is not returning the expected results. The first custom function returns what I expect and based on this the second should too.

I have attached an XL workbook with my functions and results.

The functions are:

Function LessThan40000CM(rngInput As Range, intColOffset As Integer)
Dim strCrit1 As String
strCrit1 = "<40000CM"
With Application.WorksheetFunction
LessThan40000CM = _
.SumIf(rngInput, strCrit1, rngInput.Offset(0, intColOffset))
End With
End Function

Function LessThan40020CM(rngInput As Range, intColOffset As Integer)
Dim strCrit1 As String
strCrit1 = "<40020CM"
With Application.WorksheetFunction
LessThan40020CM = _
SumIf(rngInput, strCrit1, rngInput.Offset(0, intColOffset))
End With
End Function

Any assistance would be appreciated
Thanks,
John

2. ## Re: Custom Function (2002)

The function acts correctly: since the values are text, the comparison is alphabetic. So 40010RV is less than 40020CM. The second function adds D23 and D719.

3. ## Re: Custom Function (2002)

I don't understand why you even need the custom function when you are essentially using and doing a SUMIF. You created 2 functions that could be done with 1 with only the addition of a parameter.

I don't understand what values you think should not be in the sum.
Steve

4. ## Re: Custom Function (2002)

Steve,

I originally thought of using SUMIF but decided on the custom function route. My example is a downsized version of account/account type structure. I originally tried using just the account but later determined that I should be using the account and account type combination. Hans is correct that the values are text and the comparison is alphabetic ie the number returned is not what I would have expected.

I may have to abandon my approach with the custom function if it is not feasible. One thing to keep in mind is that I need to use specific account ranges and account types to return my expected results.

Am I on the wrong path?

Thanks,
John

5. ## Re: Custom Function (2002)

Are you just looking for:
<pre>=SUMIF(A:A,"<=40000CM",D)</pre>

This stops all the "40000RV" from being "picked up" or even:

<pre>=SUMIF(A:A,"<40000R",D)</pre>

Steve

6. ## Re: Custom Function (2002)

Steve,

Utimately I would be using something like: sum the accounts between and including 40000 and 40030 where the account type is CM

John

7. ## Re: Custom Function (2002)

Multiconditionals require an ARRAY formula (confirm with ctrl-shift-enter): this should do what you are asking about:

<pre>=SUM(IF((\$C\$2:\$C\$33="CM")*(\$B\$2:\$B\$33>=40000) *(\$B\$2:\$B\$33<=40030),\$D\$2:\$D\$33))</pre>

It will sum D233 whenever the value in the row of COl C ="CM", AND the value in the row of col B >=40000 AND the value in the row of col B <=40030 .

Instead of hard coding the acct and and acct type you could also use cell references or range names.
NOTE: you will not be able to use the entire column as the reference, this is not allowed in arrays. You could use (eg C2:C65536) all the ranges must be of the same size.

Steve

8. ## Re: Custom Function (2002)

Steve,

Thank you very much. I was toying around with the SUM IF and was so close.

John

9. ## Re: Custom Function (2002)

<pre>=SUMPRODUCT((\$C\$2:\$C\$33="CM")*(\$B\$2:\$B\$33>=40 000)*(\$B\$2:\$B\$33<=40030),\$D\$2:\$D\$33)</pre>

It does not require the ctrl-shift-enter, as it is a "normal" formula. The Array is more general and can also be used to get min, max, average, stddev, etc of the rows matching the criteria.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•