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

 

[jorismoerings]

Hi,

The formula for determining the first character is a array formula which, if properly confirmed, will not return an array with the numbers of every cell in the table.
This can be fixed with adding a helper column like this:

Book1.xlsx
	A	B	C	D	E	F	G
7	proj #	status	Revenue	Helper Column
8	8976A9087	1	8	A			16
9	90A12345	1	8	A
Sheet3

Cell Formulas
Range	Formula
G8	G8	=SUMPRODUCT(--(Tbl_Backlog[Helper Column]=Product_Specific),--(Tbl_Backlog[status]=Year_current),--(Tbl_Backlog[Revenue]<poc_amount),Tbl_Backlog[Revenue])
D8:D9	D8	=MID([@[proj '#]],MATCH(TRUE,ISERROR(VALUE(MID([@[proj '#]],ROW(INDIRECT("1:"&LEN([@[proj '#]]))),1))),0),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
poc_amount	=Sheet3!$B$3	G8
Product_Specific	=Sheet3!$B$1	G8
Year_current	=Sheet3!$B$2	G8

Hope this helps.

 

[gberg]

jorismoerings,

Thanks for the idea. I usually try to avoid "helper" columns, just one more thing that someone can screwup and it's amazing how people can screw up a spreadsheet! If it is the only way, I guess it's the only way.

Thanks again

Greg

 

[jorismoerings]

Perhaps is a power-query and using the data model a better solution.
It takes calculations to the data model and not in the table anymore. No calculations - no screw ups.
But every solution has it specific perks so ….

 

[Peter_SSs]

I usually try to avoid "helper" columns

So see if this works for you

=LET(rev,Tbl_Backlog[Revenue],proj,Tbl_Backlog[proj '#],m,MID(proj,SEQUENCE(LEN(proj)),1),SUM(FILTER(rev,(LEFT(CONCAT(IF(ABS(CODE(m)-77.5)<13,m,"")),1)=Product_Specific)*(Tbl_Backlog[status]=Year_current)*(rev<poc_amount),0)))

 

[gberg]

Give me a #CALC! error

 

[Peter_SSs]

Give me a #CALC! error

Then perhaps you could give us a small set of your sample data with XL2BB?

Here is the formula working for me (at least as I understand the problem), so it would be good to work out what is fundamentally different between our worksheets.
Is 12 the result that you would expect from my sample data?

gberg.xlsm
	B	C	D	E	F	G
1	A
2	1
3	10
4
5
6		proj #	status	Revenue
7		8976A9087	1	8
8		90A12345	1	20		12
9		7Bhjuy	1	10
10		Aysewere	1	4
11		71-A DD	2	5
12
Sheet1

Cell Formulas
Range	Formula
G8	G8	=LET(proj,Tbl_Backlog[proj '#],m,MID(proj,SEQUENCE(LEN(proj)),1),SUM(FILTER(Tbl_Backlog[Revenue],(LEFT(CONCAT(IF(ABS(CODE(m)-77.5)<13,m,"")),1)=Product_Specific)*(Tbl_Backlog[status]=Year_current)*(Tbl_Backlog[Revenue]<poc_amount),0)))

Named Ranges
Name	Refers To	Cells
poc_amount	=Sheet1!$B$3	G8
Product_Specific	=Sheet1!$B$1	G8
Year_current	=Sheet1!$B$2	G8

 

[gberg]

Book1
	A	B	C
1			Revenue
2	Product	D	$ 50,000
3
4	Year	2021
5	POC Amount	3,000,000
6
7	Proj #	Status	Revenue
8	71D0001	2021	10,000
9	71G0002	2021	20,000
10	71-P5621	2021	30,000
11	328D354	2021	40,000
Sheet1

 

[Phuoc]

Not checked:

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

 

[gberg]

Column B2 = Product_Specific
Column B4 = Year_Current
Column B5 = POC_Amount

in this example I want the Product "D" summed.

That would be line 8 and line 11 or $50,000 since those lines have "D" as the first letter in the string