SUMIF when cell contains text

[freebirdwr]

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>


​

 

[skorpionkz]

Can you use VBA?

 

[Rick Rothstein]

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

 

[skorpionkz]

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
2. Open your NEW workbook
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

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:

=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")

 

[kumarapush123]

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.

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>

 

[Rick Rothstein]

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

Function SumIt() As Double
  Dim Addr As String, Header As String
  Addr = Intersect(Application.Caller.EntireRow, Columns("A:E")).Address
  Header = Left(Cells(1, Application.Caller.Column).Value, 1)
  SumIt = Evaluate(Replace("SUM(1*IF(LEFT(@)=LEFT(""" & Header & """),IF(@="""",0,RIGHT(@)),0))", "@", Addr))
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).