# Need Formula To Solve Answer In This Excel File

#### 861171314

##### New Member
Hi Friends
I Need Formula For Yellow Cell In YBC File
No Vb
It Must Work For All Combination Of Letters With Diffrent Length

And Formula For Answer Of Color File

Thanks

#### Attachments

• 8.7 KB Views: 9
• 8.7 KB Views: 10
Last edited:

#### vletm

##### Excel Ninja
861171314
AZXBCY ... Sum ... 5 ? really ?

25
Excuse Me

#### bosco_yip

##### Excel Ninja

In "Sheet1" G4, formula copied down :

=IFERROR(INDEX(\$C:\$C,AGGREGATE(15,6,ROW(\$C\$4:\$C\$10)/ISNUMBER(FIND(\$B\$4:\$B\$10,\$E\$4)),ROWS(\$1:1))),"")

Regards
Bosco

Last edited:

#### bosco_yip

##### Excel Ninja
For Yellow Cell In "YBC" File

1] Please explain how does "YBC" convert to 246 ?

2] In "Sum" H5, enter :

=SUMPRODUCT(0+MID(G5,ROW(INDIRECT("1:"&LEN(G5))),1))

Regards
Bosco

#### 861171314

##### New Member
YBC From Table Become 246

#### 861171314

##### New Member

In "Sheet1" G4, formula copied down :

=IFERROR(INDEX(\$C:\$C,AGGREGATE(15,6,ROW(\$C\$4:\$C\$10)/ISNUMBER(FIND(\$B\$4:\$B\$10,\$E\$4)),ROWS(\$1:1))),"")

Regards
Bosco
Thank U Very Much

#### bosco_yip

##### Excel Ninja

For "YBC" sheet file

1] In G5, array formula copied right to H5 and all copied down :

=SUM(INDEX(\$C\$4:\$C\$9,N(IF(1,FIND(MID(\$F5,ROW(INDIRECT("1:"&LEN(\$F5))),1),"ABCXYZ"))))*IF(G\$4="Sum",1,10^(LEN(\$F5)-ROW(INDIRECT("1:"&LEN(\$F5))))))

p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke

Regards
Bosco

#### Attachments

• 11.7 KB Views: 8
Last edited:

#### 861171314

##### New Member
View attachment 57513

For "YBC" sheet file

1] In G5, array formula copied right to H5 and all copied down :

=SUM(INDEX(\$C\$4:\$C\$9,N(IF(1,FIND(MID(\$F5,ROW(INDIRECT("1:"&LEN(\$F5))),1),"ABCXYZ"))))*IF(G\$4="Sum",1,10^(LEN(\$F5)-ROW(INDIRECT("1:"&LEN(\$F5))))))

p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke

Regards
Bosco
Thanks a lot

#### vletm

##### Excel Ninja
861171314
No Vb,
with UDF
with use of write needed cells and ranges to UDF as in samples
but without all possible error handling!

#### Attachments

• 15.3 KB Views: 4

#### 861171314

##### New Member
861171314
No Vb,
with UDF
with use of write needed cells and ranges to UDF as in samples
but without all possible error handling!

#### 861171314

##### New Member
View attachment 57513

For "YBC" sheet file

1] In G5, array formula copied right to H5 and all copied down :

=SUM(INDEX(\$C\$4:\$C\$9,N(IF(1,FIND(MID(\$F5,ROW(INDIRECT("1:"&LEN(\$F5))),1),"ABCXYZ"))))*IF(G\$4="Sum",1,10^(LEN(\$F5)-ROW(INDIRECT("1:"&LEN(\$F5))))))

p.s. array formula to be confirmed enter with Shift+Ctrl+Enter 3 keystroke

Regards
Bosco
excuse me
sum is correct in H5 but convert to number in incorrect if we change B to 12 for example

#### Attachments

• 62.4 KB Views: 7

#### bosco_yip

##### Excel Ninja
excuse me
sum is correct in H5 but convert to number in incorrect if we change B to 12 for example

For "YBC" sheet file, revision in changing "Convert Table" number to 1~2 digits

1] Select G5 >> Define Name >>

Name : MatchNum

Refers to : =INDEX(\$C\$4:\$C\$9,N(IF(1,FIND(MID(Sheet1!\$F5,ROW(INDIRECT("1:"&LEN(\$F5))),1),"ABCXYZ"))))

>> OK

Then,

in G5 enter formula and copied down :

=IFERROR(INDEX(MatchNum,1),"")&IFERROR(INDEX(MatchNum,2),"")&IFERROR(INDEX(MatchNum,3),"")&IFERROR(INDEX(MatchNum,4),"")&IFERROR(INDEX(MatchNum,5),"")&IFERROR(INDEX(MatchNum,6),"")

2] In H5 formula copied down :

=SUM(MatchNum)

Regards
Bosco

#### Attachments

• 12.7 KB Views: 10
Last edited:

#### 861171314

##### New Member
View attachment 57546

For "YBC" sheet file in changing "Convert Table" number to 2 digits

1] Select G5 >> Define Name >>

Name : MatchNum

Refers to : =INDEX(\$C\$4:\$C\$9,N(IF(1,FIND(MID(Sheet1!\$F5,ROW(INDIRECT("1:"&LEN(\$F5))),1),"ABCXYZ"))))

>> OK

Then, in G5 enter formula and copied down :

=IFERROR(INDEX(MatchNum,1),"")&IFERROR(INDEX(MatchNum,2),"")&IFERROR(INDEX(MatchNum,3),"")&IFERROR(INDEX(MatchNum,4),"")&IFERROR(INDEX(MatchNum,5),"")&IFERROR(INDEX(MatchNum,6),"")

2] In H5 formula copied down :

=SUM(MatchNum)

Regards
Bosco
Hi
bosco_yip
Thank you
it was perfect
how you can write formula such good And perfect ?