Results 1 to 9 of 9

20140124, 20:38 #1
 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!

20140124, 21:02 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,733
 Thanks
 126
 Thanked 686 Times in 623 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
Last edited by Maudibe; 20140124 at 21:26.

The Following User Says Thank You to Maudibe For This Useful Post:
afm1985 (20140125)

20140124, 23:17 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,495
 Thanks
 33
 Thanked 63 Times in 59 Posts
I think this nonmacro 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)))))))

The Following User Says Thank You to kweaver For This Useful Post:
afm1985 (20140125)

20140124, 23:40 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,733
 Thanks
 126
 Thanked 686 Times in 623 Posts
KW,
Right on! Our numbers match.

The Following User Says Thank You to Maudibe For This Useful Post:
afm1985 (20140125)

20140125, 07:45 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
A nonUDF and nonarray 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

The Following User Says Thank You to sdckapr For This Useful Post:
afm1985 (20140125)

20140125, 07:59 #6
 Join Date
 Sep 2013
 Posts
 34
 Thanks
 16
 Thanked 0 Times in 0 Posts
Thanks you very much.It`s perfect!!!!!

20140125, 08:17 #7
 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?

20140125, 09:43 #8
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,570
 Thanks
 384
 Thanked 1,484 Times in 1,348 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
Code:Case "DET", "C", "" Total = Total
Last edited by RetiredGeek; 20140125 at 09:46.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
afm1985 (20140125)

20140125, 11:05 #9
 Join Date
 Sep 2013
 Posts
 34
 Thanks
 16
 Thanked 0 Times in 0 Posts
It`s perfect! Thanks. You can close the topic