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

 

[Peter_SSs]

I think I will just go with the "helper" column,

In that case I am suggesting an alternative non-volatile formula for that, together with the corresponding updated SUM formula.
Remember too that the helper column can be hidden if you want.

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

Cell Formulas
Range	Formula
C2	C2	=LET(rev,Tbl_Backlog[Revenue],SUM(FILTER(rev,(Tbl_Backlog[FirstLetter]=Product_Specific)*(Tbl_Backlog[status]=Year_current)*(rev<poc_amount),0)))
D8:D13	D8	=LET(p,[@[proj '#]],MID(p,MATCH(TRUE,ABS(CODE(MID(p,SEQUENCE(LEN(p)),1))-77.5)<13,0),1))

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

 

[gberg]

Peter,

I can't say I understand your formulas but they do work very well. Thanks for your help and suggestions, I will put them to good use.

Thanks,

Greg

 

[Peter_SSs]

You're welcome.

I forgot to state specifically that my 'FirstLetter' formula is assuming upper case letters only, as that is what your samples were like. If it is possible the first letter could be lower case, a small adjustment would be required.

 

[gberg]

They should all be upper case, but just in case what would the adjustment be?

 

[Peter_SSs]

They should all be upper case, but just in case what would the adjustment be?

The addition of the red here
=LET(p,[@[proj '#]],MID(p,MATCH(TRUE,ABS(CODE(MID(UPPER(p),SEQUENCE(LEN(p)),1))-77.5)<13,0),1))

 

[gberg]

Awesome, thanks again!!!!!!!

 

[Peter_SSs]

No problem.