Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Function (2002)

    Steve,

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

    John

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Custom Function (2002)

    Forgot about this one:
    <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
  •