# Posts by bosco_yip

• ## Identify Row Number on Different Sheet to complete COUNTIF on (in single formula)

Try the formula solution of which the criteria were not in the same order of the source table.

In B2, formula copied down :

=COUNTIF(OFFSET(INDEX(Sheet2!A:A,MATCH(A2,Sheet2!A:A,0)),,,,1000),">0")

• ## Finding close text matches from a list of values

Try,

Select A2:A5 >> Conditional Formatting >> New rule >> Use a formula ......>>

>> The rule formula enter :

=OR(0+LEFT(A2,3)=MAX(IF(LEFT(A\$2:A\$5,3)<=LEFT(D\$2,3),0+LEFT(A\$2:A\$5,3))),0+LEFT(A2,3)=MIN(IF(LEFT(A\$2:A\$5,3)>=LEFT(D\$2,3),0+LEFT(A\$2:A\$5,3))))

>> OK

• ## Resume automatic counting

1] In B1 enter : 101

2] In B2, formula copied down :

=IF(MOD(ROW(A2),51)=0,"DT",MAX(B\$1:B1)+1)

• ## Count formula based on a column heading

In C20, formula copied down :

=COUNTIFS(\$2:\$2,"Saturday",4:4,"*")

• ## Help with formula

Try

In E11, formula copied down :

=IF(C11="","",IFERROR(INDEX(F\$2:F\$4,LOOKUP(9,MATCH(B\$1:B11,E\$2:E\$4,0)))*(C11-INDEX(I\$2:I\$4,LOOKUP(9,MATCH(B\$1:B11,E\$2:E\$4,0)))),""))

• ## Match tabname and vlookup

This part : MID(CELL("filename",\$A\$1),FIND("]",CELL("filename",\$A\$1))+1,32)

will return the current sheet name.

• ## Match tabname and vlookup

In each Sheet of ,"IBM", "Accenture"…... C2, formula copied down :

=IF(SUMIFS(Worksheet!\$C:\$C,Worksheet!\$A:\$A,MID(CELL("filename",\$A\$1),FIND("]",CELL("filename",\$A\$1))+1,32),Worksheet!\$B:\$B,[@Name])=[@Value],[@Name],"N/A")

• ## Match tabname and vlookup

Try,

In "Worksheet" C2, formula copied down :

=VLOOKUP([@[Name (provided)]],INDIRECT("'"&[@[Account (provided)]]&"'!A:B"),2,0)

• ## Sumifs For Resource Allocation - Resource Criteria is Seperated with ";"

Thanks for the reply - that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.

Cheers

Joe

This is the limitation of formula solution, for large range of data you should switch to VBA solution or Power Query.

• ## Sumifs For Resource Allocation - Resource Criteria is Seperated with ";"

Try this formula solution for Excel 2019 and above.

1] Create 2 define names as per attachment

2] In "Intermate Table" H3, formula copied across down :

=IFERROR(IF(LEFT(H\$2)="P",TableRgn/IndexRgn,TableRgn),"")

3] In "Output Table " B9, formula copies across and down :

=SUMIFS(I:I,\$H:\$H,\$A9)

4] In "Output Table " B12, formula copies across :

=SUM(B9:B11)

• ## VLookup

1] In Sheet "A" C6, formula copied right to D6 and all copied down :

=INDEX(B!F:F,LOOKUP(1,0/FIND(\$A6,B!\$A\$6:\$A\$11),ROW(B!\$A\$6:\$A\$11)))

2] In Sheet "B" C6, formula copied down :

=TEXTJOIN(", ",,IFERROR(LOOKUP(0+MID(A6,{1,3},1),A!\$A:\$B),""))

• ## cutting words with Len and Left and vlookup

In L2, formula copied down :

=LEFT(A5,FIND(" ",A5)+2)

• ## function to find out format

Give few lines of example data and the expected result

• ## Count Based On Specific Criteria and Reset If Blank Encountered

In H2, formula copied down :

=IF(A2="","",COUNT(G2:G\$37)-SUM(H3:H\$37))

• ## Question a propos d'une méthode

Maybe,

In D1, formula copied down :

=IFERROR(INDEX(A\$1:A\$7,MOD(ROW(A1)-1,7)+1)&"-"&INDEX(B\$1:B\$3,INT((ROW(A1)-1)/7)+1),"")

• ## how to extract 10 digit mobile number within text or specialcharacter

Thank you it's working perfectly

and one more help if cell have 3 or 4 mobile number how to extract with this formula

One post one question.

Please open a new post for your new question, together with few lines of example data and the expected result.

• ## Count from Unique Criteria

In G2, enter formula :

=SUMPRODUCT((E2:E17=F2)/COUNTIFS(E2:E17,E2:E17,A2:A17,A2:A17,C2:C17,C2:C17))

• ## how to extract 10 digit mobile number within text or specialcharacter

B2, copied across right and all copied down :

=MID(\$A2,FIND(B\$1,\$A2)+LEN(B\$1)+2,10)

• ## Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Then,

Change B1 formula to :

=0+SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,99)," ",REPT(" ",50)),50),"\$",""),"c","%")

and copied down

• ## Extract numbers after specific text in a text string with bracket for negative values and divide the number by 100 if letter 'c' associated with number

Try,

In B1, formula copied down :

=0+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,FIND("vs",A1)-FIND("EPS",A1)-5),"\$",""),"c","%")