sum alphanumeric cells

[JimmyD]

I am trying to sum alphanumeric cells, the following cells have:

A1= Mon, A2= blank
B1= Tue, B2= 3
C1= Wed, C2= 4a
D1=Thur, D2= blank
E1= Fri, E2= 3s
F1= Sat, F2= 1a
G1= Sun, G2= 2s
H1= Mon, H2= 4
i1= Tue, i2= 7s
J1=Wed, J2= blank
K1=Thur, K2= blank
L1=Fri, L2= 1
M1=Sat, M2= 8
N1=Sun, N2= blank
O1=Mon, O2= 6a

"a" represents Annual Leave, "s" represents Sick Leave, and a number
represents Over Time. The numbers, alphanumeric numbers, and blank
cells can randomly be in any day of the week cell/column. My goal is
to sum cells that have "a" and sum cells that have "s" and sum cells
that have a number seperately, example...

A4 = "Over Time"
A5 = "16" (3 + 4 + 1 + 8 = 16)
B4 = "Annual Leave"
B5 = "11" (4a + 1a + 6a = 11)
C4 = "Sick Leave"
C5 = "12" (3s + 2s + 7s = 12)

Can anyone please help me...?
Sincerely,
JimmyD

 

[Gary Keramidas]

maybe you can adapt something like this

Option Explicit
Dim cell As Range
Dim i As Long
Dim j As Variant

Sub test()
For Each cell In Range("a1:a6") ' change to your range
For i = 1 To Len(cell)

j = (Mid(cell, i, 1))
Debug.Print j
If j > Chr(48) And j < Chr(57) Then
MsgBox j
End If
Next i
Next cell
End Sub

 

[Norman Jones]

Hi Jimmy,

Try the following function:

'=============>>
Public Function AlfaSum(Rng As Range, _
Letter As String) As Long
Dim rCell As Range
Dim sStr As String
Dim vVal As Variant
Dim i As Long

For Each rCell In Rng.Cells
With rCell
sStr = .Value
i = Len(sStr)
If i > 0 Then
vVal = Left(sStr, i - 1)

If Right(sStr, 1) = LCase(Letter) _
And IsNumeric(vVal) Then
AlfaSum = AlfaSum + vVal
End If
End If
End With
Next rCell

End Function
'<<=============

In your example:

A4: Over Time
A5: =SUM(A2:O2) ==> 16
B4: Annual Leave
B5 =AlfaSum(A2:O2,"A") ==> 11
C4: Sick Leave
C5: =AlfaSum(A2:O2,"s") ==> 12

 

[JimmyD]

I should have mentioned that I am a beginner with excel...
I tried the following example below... it did not work for me.
The other example was too complx for me... I did not know how to
incorporate it into excel.

I am very grateful for the quick response, advice given. I will try
the example below again. It coul be me... how I am typing it in the
cells of B5, and C5 respectfully... cell A5 works just fine.

Is there a simple solution to it?

Thank you...

 

[Norman Jones]

Hi Jimmy,

I should have mentioned that I am a beginner with excel...
I tried the following example below... it did not work for me.
The other example was too complx for me... I did not know how to
incorporate it into excel.

As you are not familiar with User Defined Functions, you may wish to visit
David McRitchie's 'Getting Started With Macros And User Defined Functions'
at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Ron Rosenfeld]

I am trying to sum alphanumeric cells, the following cells have:

A1= Mon, A2= blank
B1= Tue, B2= 3
C1= Wed, C2= 4a
D1=Thur, D2= blank
E1= Fri, E2= 3s
F1= Sat, F2= 1a
G1= Sun, G2= 2s
H1= Mon, H2= 4
i1= Tue, i2= 7s
J1=Wed, J2= blank
K1=Thur, K2= blank
L1=Fri, L2= 1
M1=Sat, M2= 8
N1=Sun, N2= blank
O1=Mon, O2= 6a

"a" represents Annual Leave, "s" represents Sick Leave, and a number
represents Over Time. The numbers, alphanumeric numbers, and blank
cells can randomly be in any day of the week cell/column. My goal is
to sum cells that have "a" and sum cells that have "s" and sum cells
that have a number seperately, example...

A4 = "Over Time"
A5 = "16" (3 + 4 + 1 + 8 = 16)
B4 = "Annual Leave"
B5 = "11" (4a + 1a + 6a = 11)
C4 = "Sick Leave"
C5 = "12" (3s + 2s + 7s = 12)

Can anyone please help me...?
Sincerely,
JimmyD

You can do it with formulas:

A5: =SUM(rng)
B5: =SUM(IF(RIGHT(rng,1)="a",--LEFT(rng,LEN(rng)-1),0))
C5 =SUM(IF(RIGHT(rng,1)="s",--LEFT(rng,LEN(rng)-1),0))

**Important Notes**

1. Substitute your range of cells for the "rng" term in the formulas. e.g.
a22. Or, more simply, you can name that range "rng" by selecting
Insert/Name/Define
Names in Workbook: rng
Refers To: A2:O2
OK


2. The formulas in B5 and C5 are **array** formulas. After you type or
copy/paste them into the cell, instead of holding down <enter>, hold down
<ctrl><shift> while hitting <enter>. If you do it correctly, Excel will place
braces {...} around the formulas.


--ron