# Thread: UDF for Sum alpha and numeric caracter

1. ## 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!

2. 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```

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

afm1985 (2014-01-25)

4. 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. KW,

Right on! Our numbers match.

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

afm1985 (2014-01-25)

8. 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. Thanks you very much.It`s perfect!!!!!

11. 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. 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

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

afm1985 (2014-01-25)

14. 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
•