Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts

    UDF for Sum alpha and numeric caracter

    Hello!
    I have rows with 31 cell with numeric and alpha caracters like in exemple attached and i want to sum those 31 cell .I would like an UDF functios with these conditions:
    1) Z = 24
    2) T = 16
    3) DET = 0
    4) C = 0
    5) N = 6
    6) if cell (numeric) from range > 8 cell =8
    7) if cell (numeric) from range < 8 cell value
    8) If cell is blank cell value = 0
    I hope i was explicit! Thanks in advance!
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    afm,

    In AH3 enter the formula =SumRow(C3:AG3) then copy down. Although your conditions did not specify what to do if the cell is exactly 8, I set the value to 8.

    HTH,
    Maud

    grid2.png
    Code:
    Public Function SumRow(rng As Range)
    Application.Volatile
    Dim cell As Range
    Total = 0
    For Each cell In rng
        Select Case cell
            Case "Z"
                Total = Total + 24
            Case "T"
                Total = Total + 16
            Case "DET", "C", ""
                Total = Total
            Case "N"
                Total = Total + 6
            Case Is >= 8  'REMOVE EQUAL SIGN IF 8 SHOULD NOT SET A VALUE OF 8
                Total = Total + 8
            Case Is < 8
                Total = Total + cell
       End Select
    Next cell
    SumRow = Total
    End Function
    Attached Files Attached Files
    Last edited by Maudibe; 2014-01-24 at 21:26.

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    afm1985 (2014-01-25)

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I think this non-macro ARRAY formula works...fill down from the first total cell:

    =SUM(IF("Z"=C3:AG3,24,IF("T"=C3:AG3,16,IF("DET"=C3 :AG3,0,IF("C"=C3:AG3,0,IF("N"=C3:AG3,6,IF(C3:AG3>= 8,8,C3:AG3)))))))

  5. The Following User Says Thank You to kweaver For This Useful Post:

    afm1985 (2014-01-25)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    Right on! Our numbers match.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    afm1985 (2014-01-25)

  8. #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
    A non-UDF and non-array formula that can be used is:
    =COUNTIF(C3:AG3,"Z")*24+COUNTIF(C3:AG3,"T")*16+COU NTIF(C3:AG3,"N")*6+COUNTIF(C3:AG3,">=8")*8+SUMIF(C 3:AG3,"<8")

    Also this formula will not run into the nested IF max if more conditions are added.
    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    afm1985 (2014-01-25)

  10. #6
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks you very much.It`s perfect!!!!!

  11. #7
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    If in UDF if I put in range another alphabetic caracter by exemple a,b etc in sumrow it is +8. what can be do to except all alpha caracter wiche are not in list to be an exception and to be equal with 0?

  12. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    afm,

    I think this will make it work the way you want.
    Code:
    Public Function SumRow(rng As Range)
    Dim cell As Range
    Total = 0
    For Each cell In rng
        Select Case cell
            Case "Z"
                Total = Total + 24
            Case "T"
                Total = Total + 16
            Case "DET", "C", ""
                Total = Total
            Case "N"
                Total = Total + 6
            Case Is >= 8
                If IsNumeric(cell) Then 'Test for number
                  Total = Total + 8
                End If
            Case Is < 8
                Total = Total + cell
       End Select
    Next cell
    SumRow = Total
    End Function
    Also, with this change I believe these two lines become unnecessary.
    Code:
            Case "DET", "C", ""
                Total = Total
    HTH
    Last edited by RetiredGeek; 2014-01-25 at 09:46.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. The Following User Says Thank You to RetiredGeek For This Useful Post:

    afm1985 (2014-01-25)

  14. #9
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    It`s perfect! Thanks. You can close the topic

Posting Permissions

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