# SUMIF when cell contains text

#### freebirdwr

##### New Member
I have a spreadsheet where I need to sum some cells where the cell contains text and a number.
For instance, if the cell contains an "A", then I need to sum the numbers in those cells. If "S", same thing. Is there a way to use a SUMIF with right(text,1) since the number in each cell will always be a single digit?

Sorry for the table. I don't know how to copy an Excel table to a Post.
 A B C D E F G 1 MON TUE WED THU FRI A-Used S-Used 2 S-4 A-8 A-4 12 4 3 A-6 6

<TBODY>
</TBODY>
﻿﻿
﻿﻿​

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Can you use VBA?

I have a spreadsheet where I need to sum some cells where the cell contains text and a number.
For instance, if the cell contains an "A", then I need to sum the numbers in those cells. If "S", same thing. Is there a way to use a SUMIF with right(text,1) since the number in each cell will always be a single digit?

Sorry for the table. I don't know how to copy an Excel table to a Post.
 A B C D E F G 1 MON TUE WED THU FRI A-Used S-Used 2 S-4 A-8 A-4 12 4 3 A-6 6

<tbody>
</tbody>
Put this array-entered** formula in cell F2 and copy it across to G2, then copy F2:G2 down to the bottom of your data...

=SUM(1*IF(LEFT(\$A2:\$E2)=LEFT(F\$1),IF(\$A2:\$E2="",0,RIGHT(\$A2:\$E2)),0))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

If you can use VBA below code will define the Formula which will do the thread.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Code:
``````Function SUMLT(rng As Range, sUsed As String) As Long

Dim cell As Range
Dim dgt As Long

sUsed = Left(sUsed, 1)
SUMLT = Empty
For Each cell In rng
If Left(cell, 1) = sUsed Then
dgt = Right(cell, 1)
SUMLT = SUMLT + dgt
End If
Next cell

End Function``````

in cell F2 type:
Code:
``=sumlt(A2:E2,F1)``
1. provide range you need to calculate (in your sample in row to select cells from A to E)
2. provide spell you need to sum (in your case select cell F1 for A and G1 for S, or type "A" or "G")

Try Defining a UDF (User Defined Function) as below and enter the Formula =Asum(3) or =Asum(4) in the cell where you want to display the Sum.
Code:
``````Function ASum(RowNum As Integer) As Double
Dim Sum1 As Double
Sum1 = 0
For i = 1 To 10
Val1 = Sheets("Sheet1").Cells(RowNum, i)
Flag1 = VBA.UCase(VBA.Left(Val1, 1))
If (Flag1 = "A") Or (Flag1 = "S") Then
Sum1 = Sum1 + VBA.Val(VBA.Right(Val1, 1))
End If
Next i
ASum = Sum1
End Function``````

If you are not sure about how to define and use a UDF, please refer this link <UDF Creation>

Personally, I would recommend using the formula I posted in Message #3 over the use of a UDF (the formula will be more efficient); however, given the data structure posted by the OP, this non-looping UDF could be considered...

Code:
``````Function SumIt() As Double
End Function``````
Then put this formula...

=SumIt()

in the cells you want values displayed in (F2:G3 for the data posted, although more columns could be placed after Column G if other letters are involved and the number of rows can be increased as needed as well).

Replies
4
Views
86
Replies
0
Views
81
Replies
10
Views
220
Replies
7
Views
98
Replies
5
Views
165

### Forum statistics

1,203,759
Messages
6,057,191
Members
444,913
Latest member
ILGSE ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back