First Letter in a string

[gberg]

I have this formula that I need to modify a bit. Currently it takes the first character in a string and compares it to a cell I've called "Product_Specific". The Product_Specific cell will contain one letter (i.e. A, B, C, D, ect.) The formula sums up all of the rows that start with the letter entered into the "Product_Specific" cell. The text in red below is the part of the formula that looks for the letter

=SUMPRODUCT(--(LEFT(Tbl_Backlog[Proj '#],1)=Product_Specific),--(Tbl_Backlog[Status]=Year_Current),--(Tbl_Backlog[Revenue]<POC_Amount),Tbl_Backlog[Revenue])

My issue is the cells all used to start with a letter (i.e. D0001, D0002, Dold, Dxxx, etc.) but now they could remain this way or they could start with something else (i.e. 71D0001, 71-D0001, etc.) If there is something in front of the first letter it will always be a number or a character such as a "-". What I want is to find the first letter in the string to compare it to the "Product_Specific" cell.

I have this formula that works to pull the first letter from a single cell
=MID(A18,MATCH(TRUE,ISERROR(VALUE(MID(A18,ROW(INDIRECT("1:"&LEN(A18))),1))),0),1)

I tried using this in the original formula above but replace the "A18" cell reference with "Tbl_Backlog[Proj '#]" but it does not work for me
=SUMPRODUCT(--(MID(Tbl_Backlog[Proj '#],MATCH(TRUE,ISERROR(VALUE(MID(Tbl_Backlog[Proj '#],ROW(INDIRECT("1:"&LEN(Tbl_Backlog[Proj '#]))),1))),0),1)=Product_Specific),--(Tbl_Backlog[Status]=Year_Current),--(Tbl_Backlog[Revenue]<POC_Amount),Tbl_Backlog[Revenue])

I hope this makes sense and would be grateful for any help

Thanks,

Greg

 

[Phuoc]

Or try:

=SUMIFS(C8:C11,A8:A11,"*"&B2&"*",B8:B11,B4,C8:C11,"<"&B5)

 

[gberg]

Phuoc,

That seems to work. I want to do a little more testing but that seems to have done the trick!!!!!!!

 

[gberg]

Phuoc,

Your original formula '=SUMPRODUCT(--(ISNUMBER(FIND(Product_Specific,Tbl_Backlog[Proj '#]))),--(Tbl_Backlog[Status]=Year_Current),--(Tbl_Backlog[Revenue]<POC_Amount),Tbl_Backlog[Revenue]) defiantly works!!!!!!!!

This is an awesome site with a bunch of really smart people! I appreciate everyone's help and feedback. I can't thank you enough!

Greg

 

[Peter_SSs]

Your original formula '=SUMPRODUCT(--(ISNUMBER(FIND(Product_Specific,Tbl_Backlog[Proj '#]))),--(Tbl_Backlog[Status]=Year_Current),--(Tbl_Backlog[Revenue]<POC_Amount),Tbl_Backlog[Revenue]) defiantly works!!!!!!!!

If that is the case, and I am not doubting it, there must only ever be one letter in the string.
I took the statement below (& the thread title) to indicate that there might be several letters in the string.

What I want is to find the first letter in the string to compare it to the "Product_Specific" cell.

Having said that, there was a flaw in the logic of my previous suggestion. Instead, my suggestion would become C2 or C3 depending on whether you have the LET function or not.

gberg.xlsm
	A	B	C
1
2		D	50000
3			50000
4		2021
5		3000000
6
7	proj #	status	Revenue
8	71D0001	2021	10000
9	71G0002	2021	20000
10	71-P5621	2021	30000
11	328D354	2021	40000
12
Sheet1

Cell Formulas
Range	Formula
C2	C2	=LET(rev,Tbl_Backlog[Revenue],SUM(FILTER(rev,ISNUMBER(FIND(Product_Specific,Tbl_Backlog[proj '#]))*(Tbl_Backlog[status]=Year_current)*(rev<poc_amount),0)))
C3	C3	=SUM(FILTER(Tbl_Backlog[Revenue],ISNUMBER(FIND(Product_Specific,Tbl_Backlog[proj '#]))*(Tbl_Backlog[status]=Year_current)*(Tbl_Backlog[Revenue]<poc_amount),0))

Named Ranges
Name	Refers To	Cells
poc_amount	=Sheet1!$B$5	C2:C3
Product_Specific	=Sheet1!$B$2	C2:C3
Year_current	=Sheet1!$B$4	C2:C3

 

[gberg]

Peter,

I now see the issue. If the string has a "D" and an "X" it will calculate for both of these and more if there are additional letters. That does present an issue so I guess this will not work, it seemed so good but I'm glad you pointed that issue out before it became a problem. Looks like I'm back to the "helper" cell.

Thanks,

Greg

 

[Peter_SSs]

Are there any other characters that could come before the first letter apart from digits and "-" signs?

 

[gberg]

These are some examples of what we currently are using (just going with "D" as the primary example as the "Product_Specifc" reference)
D****
D*9999
DC7071
DPOC
D*6979B
DL&I
DLTDU
D
D0884X
DZ

These will all stay the same except they will have the following in front of them (it won't always be 71 but it will be some number string)
71
71-
71.
71*

 

[Peter_SSs]

To be honest, a helper column is probably simplest and since it is in a table (and could be a hidden column) so it shouldn't be too likely to get interfered with by other users.
However, if you really want to avoid that, and the only characters before the first letter are digits, ".", "-" or "*" then you could try one of these

gberg.xlsm
	A	B	C
1
2		D	65000
3			65000
4		2021
5		3000000
6
7	proj #	status	Revenue
8	71D0001	2021	10000
9	71G0002	2021	20000
10	71-PD5621	2021	30000
11	71*DP2569	2021	10000
12	328D354	2021	40000
13	D36598	2021	5000
14
Sheet1

Cell Formulas
Range	Formula
C2	C2	=LET(rev,Tbl_Backlog[Revenue],proj,1&Tbl_Backlog[proj '#],SUM(FILTER(rev,ISNUMBER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(proj,FIND(Product_Specific,proj)-1),"-",""),".",""),"*","")+0)*(Tbl_Backlog[status]=Year_current)*(rev<poc_amount),0)))
C3	C3	=SUM(FILTER(Tbl_Backlog[Revenue],ISNUMBER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(1&Tbl_Backlog[proj '#],FIND(Product_Specific,1&Tbl_Backlog[proj '#])-1),"-",""),".",""),"*","")+0)*(Tbl_Backlog[status]=Year_current)*(Tbl_Backlog[Revenue]<poc_amount),0))

Named Ranges
Name	Refers To	Cells
poc_amount	=Sheet1!$B$5	C2:C3
Product_Specific	=Sheet1!$B$2	C2:C3
Year_current	=Sheet1!$B$4	C2:C3

 

[gberg]

Peter,

I think I will just go with the "helper" column, as much as I don't like them. I "assume" that they will only use the ., -, or * but then someone will use a _ or a !, who knows and then the whole thing is messed up.

Thanks again for your help

Greg

 

[Anthony47]

If you don't like a helper column then you probably will not like vba either, but let me try:
-copy this code to a Standard Module of your vba-project

Function TableSumIfs(ByRef myValues As Range, ByRef myF1 As Range, ByVal myFV1, Optional ByRef myF2 As Range, Optional myFV2 = True, Optional myF3 As Range, Optional myFV3 = 999999999) As Double
Dim F1 As Boolean, F2 As Boolean, F3 As Boolean, iTot As Double, myCS As String
Dim I As Long, J As Long
'
For I = 1 To myValues.Rows.Count
    myCS = myF1.Cells(I, 1)
    F1 = False
    For J = 1 To Len(myCS)
        If UCase(Mid(myCS, J, 1)) = UCase(myFV1) Then F1 = True
        If Asc(Mid(myCS, J, 1)) > 64 Then Exit For
    Next J
    If myF2 Is Nothing Then
        F2 = True
    Else
        F2 = (myF2.Cells(I, 1) = myFV2)
    End If
    If myF3 Is Nothing Then
        F3 = True
    Else
        F3 = (myF3.Cells(I, 1) < myFV3)
    End If
    If F1 And F2 And F3 Then
        iTot = iTot + myValues.Cells(I, 1)
    End If
Next I
TableSumIfs = iTot
End Function

Now return to excel, and you may use the function TableSumIfs, in a formula like this:

=TableSumIfs(Tbl_Backlog[Revenue],Tbl_Backlog[Proj '#],Product_Specific,Tbl_Backlog[Status],Year_Current,Tbl_Backlog[Revenue],POC_Amount)

This version requires the following parametres:
1) the interval to be summed
2) the Proj# interval (Tbl_Backlog[Proj '#], in the above formula)
3) the Letter to be checked (Product_Specific, in the formula)
4) the Year interval (Tbl_Backlog[Status], in the formula)
5) the Year value (Year_Current, in the formula)
6) the interval to be checked (again Tbl_Backlog[Revenue], in the formula)
7) the value to be tested (POC_Amount, in the formula)
Note that parametres 6-7 are checked for 6<7; you can omit these two parametres and only the letter & year will be used for filtering the values
For example: =TableSumIfs(Tbl_Backlog[Revenue],Tbl_Backlog[Proj '#],Product_Specific,Tbl_Backlog[Status],Year_Current)

You may also omit parametres 4-5, so that the data will not be filtered by year.
For example: =TableSumIfs(Tbl_Backlog[Revenue],Tbl_Backlog[Proj '#],Product_Specific,,,Tbl_Backlog[Revenue],POC_Amount)

And of course you may omit from 4 to 7, and filter only by the letter.
For example: =TableSumIfs(Tbl_Backlog[Revenue];Tbl_Backlog[Proj '#];Product_Specific)

Bye