Help creating a formula for stock control

[conna_1996]

Hello. I am trying to create a formula that counts all the letter's different character types in the registration collum based on the plate style. for example, I want to count how letter 'a's are present for 3D Gel plate styles. I have tried the Len function which works but does not define between the plate style. I have also tried the countifs with the plate style '3D Gel' as the criteria however this only counts the Cells which contain the letter 'a' and not how many 'a's are present. Would you be able to help me with a formula? I know I will have to use the formula for all plate styles and character types, so it will be complicated, but I just can't figure out the working formula or if its even possible

 

[hagia_sofia]

Something like this?

=LET(
style,FILTER(M2:M7,S2:S7="3D Gel"),
letters,DROP(REDUCE("",style,LAMBDA(a,b,VSTACK(a,MID(b,SEQUENCE(LEN(b)),1)))),1),
counts,SORT(LET(array,(letters),HSTACK(UNIQUE(array),MAP(UNIQUE(array),LAMBDA(x,SUM(--(array=x))))))),
abc,CHAR(SEQUENCE(26,,97)),
HSTACK(abc,XLOOKUP(abc,CHOOSECOLS(counts,1),CHOOSECOLS(counts,2),0)))

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1	Car Registration	Plate Style
2	ab12 ccc	3D Gel		Option 1	5
3	aa12 ccc	4D		Option 2	5
4	ab12 aav	3D Gel
5	ab12 ccc	3D Gel
6	bb12 ccc	4D
Sheet1

Cell Formulas
Range	Formula
E2	E2	=LET(t,CONCAT(FILTER(Table1[Car Registration],Table1[Plate Style]="3D Gel",0)),SUM(--(MID(t,SEQUENCE(LEN(t)),1)="a")))
E3	E3	=SUM((Table1[Plate Style]="3D Gel")*(LEN(Table1[Car Registration])-LEN(SUBSTITUTE(Table1[Car Registration],"a",""))))