Search results

Hi GraH - Guido, You are right, I always like short and sweet formula to solve question directly without any variation. Cheers Bosco

Or, try a simple Sumproduct function formula way 1] Put criteria "worker name" in B9:B12 as per following "output table" 2] In C9, formula copied down: =SUMPRODUCT((C$2:D$6=B9)*B$2:B$6+(F$2:G$6=B9)*E$2:E$6)/2

Without helper, try this: =SUM(0+ISTEXT((UNIQUE(IF(SUBTOTAL(103,OFFSET(F9,ROW(F9:F28)-ROW(F9),0,1))*ISERR(SEARCH("user 8",F9:F28))=1,F9:F28)))))

Try this old school formula for all Excel versions. In D14, formula copy across and down: =SUM(COUNTIF(OFFSET(INDEX($A:$A,MATCH($B14,$C:$C,0)),,MATCH($B$11,$1:$1,0)-1,2,5),OFFSET($B$20,COLUMN(A:A)*2-2,,2))) Remark: It is a dynamic formula. Click B11 dropdown list to change the week number...

Please see this Unicode symbols link: https://www.vertex42.com/ExcelTips/unicode-symbols.html And, 1] Here is how to use Char or Unichar functions for superscripts and symbols 2] Please see attached file

It is called Excel shortcuts However These shortcuts work for Calibri and Arial If you are using some other font, the character codes may be different. The numbers with superscripts are turned into numeric strings, meaning you won't be able to perform any calculations with them.

To create a custom number format for superscript 3: 1] type: 0 ft 2] then press the Alt key, type 0179 on the numeric keypad, then release Alt. Remark: Superscript 2 Alt 0178 Superscript 3 Alt 0179

Or try this, In A4, enter formula: =FILTER(Table1,ISNUMBER(SEARCH(B1,Table1[ADDRESS]&Table1[STAFF])))

Here is my formula solution for all Excel versions In C3, formula copied down: =REPLACE(TEXT(TEXT(LEFT(A3,6),"00\/00\/00"),"mm/dd/yy"),7,,18+INDEX({1,1,1;2,1,1;2,2,0;2,1,1},MATCH(0+MID(A3,7,1),{0;4;5;9}),MATCH(0+MID(A3,5,2),{0,37,58})))

Just guessing, suggest you to extract the text after a blank row. If not the case, post a sample file with data as per AlanSidman mentioned in Level #2

As per the forum rule: "one post one question", Please open a new thread for your new question. Thanks

Try, 1] Set up "Source Table sheet" and "Result Table sheet" as below. 2] In "Result Table sheet" E3, enter formula and copied down : =LOOKUP(1,-SEARCH(F3,C$3:C$5),A$3:A$5) Remark: Change the set up as follow your own worksheet, and change the cells address as well.

You need the final result to be 060-MIL Then, try this simply formula in cell C2, copied down: =TEXT(A2,"000-")&B2

Try, =SUMIFS(J:J,H:H,B3)-SUMIFS(J:J,H:H,B3,I:I,C3) or =SUMIFS(J:J,H:H,B3,I:I,"<>"&C3)

Or try this shorter formula way: 1] In B2, copied down: =LEFT(A2,-LOOKUP(,-FIND(" ",A2,ROW(A:A)))-1) 2] In C2, copied down: =SUBSTITUTE(A2,B2,"")

Another option of a formula way. This is a simply formula way, with a helper column + Index & Match formula: 1] In "FOR THE MONTH OF JULY" helper colum A2, enter formula and copied down: =IF(E2="","",IF(T2='DAILY CAUSE LIST'!H$1,MAX(AC$1:AC1)+1,"")) 2] In "DAILY CAUSE LIST" range A6:J6...

2 simply Vlookup function is enough for your work. In D2, formula copied down: =IFERROR(VLOOKUP(B2,Incentive!$C$2:$E$13,(VLOOKUP(A2,Achievement!$A$2:$B$4,2,0)>=1)+2,0),"")

You can shorten the formula by : =IFERROR(LOOKUP(1,-SEARCH(J$2:J$41,D2),J$2:J$41),"") Regards

Hi, 1] Please be noted our forum rule, "one post one question" 2] In "New Joinee" sheet B2, formula copied across right and down: =IF(ISNUMBER(MATCH(B$1,0+Data!$1:$1,0)),OFFSET(Data!$A$2,ROW($A1),4+7*(COLUMN(A$1)-1)),"")

In E9 cell value to show debit -(minus) credit, try E9, enter formula: =SUMPRODUCT((F2:F8={"Dr.","Cr."})*E2:E8*{1,-1}) or, if you have newest Excel versions =SUM((F2:F8={"Dr.","Cr."})*E2:E8*{1,-1})

Then, What is your expected result in J7 ? , if the Age in J4 changed to 67.

Tried using this formula instead. In J7 =INDEX((G3:G5,G6:G8),MATCH(J3,B3:B8,0),,MATCH(J4,D5:D6))

Using a helper column, can easy solve the problem for all Excel versions. 1] Helper column, A2 formula copied down : =IF(B2="","",B2&COUNTIF(B$2:B2,B2)) Then 2] In Result, F2 formula copied down: =IFERROR(VLOOKUP(E$2&ROW(A1),A$2:C$100,3,0),"")

Formula way, In K2, formula copied across right and down: =COUNTIF(OFFSET(INDEX($C$2:$F$2,MATCH($J2,$C$1:$F$1,0)),,,230000),K$1)

Something like this: =IF(AND(K5<25%,J3="Q1"),"MINOR RISK",IF(AND(K5>25%,K5<50%,J3="Q2"),"MINOR RISK",IF(AND(K5>50%,K5<75%,J3="Q3"),"MINOR RISK",IF(AND(K5>75%,J3="Q4"),"MINOR RISK",""))))