Search results

Hi and welcome to the forum. You can use IF function like this: =IF(B2>1000000,1000000,B2) Regards,

Hi, There is Emp-1 data only, so you can adjust the SumRange for other Emp accordingly. B3: =SUMIFS(Data!G:G,Data!B:B,A1) B4: =SUMIFS(Data!G:G,Data!B:B,">"&EOMONTH(A1,-1),Data!B:B,"<="&EOMONTH(A1,0)) B5: =SUMIFS(Data!G:G,Data!B:B,">="&DATE(YEAR(A1),1,1),Data!B:B,"<="&DATE(YEAR(A1),12,31))...

Hi and Welcome to the forum :awesome: It is an {array} formula, you need to press Ctrl+Shift+Enter (not just enter) upon edit. Regards,

Hi again, Try this: =AGGREGATE(14,6,FILTERXML("<a><b>"&SUBSTITUTE(A10,"~~ ","</b><b>")&"</b></a>","//b"),1) Regards,

Hi, Recently used FILTERXML function with SUBSTITUTE: =SUMPRODUCT(FILTERXML("<a><b>"&SUBSTITUTE(A10,"~~ ","</b><b>")&"</b></a>","//b")) Regards,

Hi, Just replace the comma with semi colon in second array: =SUM(SUMIFS(Sheet3!H:H,Sheet3!G:G,{"X","Y","Z"},Sheet3!E:E,"SA",Sheet3!D:D,{"A";"B";"C"})) Regards,

Hi @GraH - Guido Happy Birthday!

Happy Birthday Alan

Hi, Got the idea :awesome: =FILTERXML("<a><b>"&SUBSTITUTE(A1,"|","</b><b>")&"</b></a>","//b") Never used FILTERXML, not even saw this function before :) Regards,

Happy New Year :awesome:

Hi Jamil, Good day and welcome to the forum :awesome: Google showed many results, here are few: https://www.myonlinetraininghub.com/excel-pivot-cache https://www.extendoffice.com/documents/excel/4238-excel-clear-filter-cache.html https://www.contextures.com/xlPivot11.html Regards,

Hi, Or this: =SUMIF($C$2:$Q$2,C14,INDEX($C$3:$Q$9,MATCH(B14,$B$3:$B$9,0),)) Regards,

Hi again, Thanks for clarification. Select your range A2:H11 > Go to Home > Conditional Formatting > Formula: =$E2>VLOOKUP($D2,INDIRECT("Table3"),2,0) or: =$E2>SUMIF($L$2:$L$5,$D2,$M$2:$M$5) Set Format and Press Ok Regards,

Hi, I didn't face any issue, may be you should try clearing your browser history, and then try again. You can also try the Stay Logged in check box: Regards,

Hi Jagdish, 1: =IFERROR(INDEX(Database!$F$2:$F$999,AGGREGATE(15,6,1/((LEFT(Database!$F$2:$F$999)="A")/(ROW($A$2:$A$999)-1)),ROW(A1)),),"") 2: not clear to me. Regards,

Hi, Or this with just enter: =IFERROR(INDEX(Data!$B$2:$H$2644,AGGREGATE(15,6,1/((Data!$A$2:$A$2644=$A$2)/(ROW(Data!$A$2:$A$2644)-1)),COUNTIF($A$2:$A2,$A2)),COLUMN(A1)),"") Regards,

Hi, PQ solution if interested. All done via GUI. Convert your data into Table, Go to Data > Get & Transform > From Table Remove Duplicate Remove Blank Rows Close & Load let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" =...

Hi Lee, Good day... You can get more targeted answers by uploading a sample version of your file with manually expected output. Regards,

Other than VBA....! New Dynamic Array Formulas in Office 365 Version. # is added for New Array Reference Notation, e.g. B2# refers to full range B2:B10

Hi @GraH - Guido Amazing. I tried with PQ Text to Column, but it didn't worked the way i wanted. Good solution. Thanks for sharing.

I assume you've solved it :cool:

Hi, Check this {array formula} =SUM((TRIM(MID(SUBSTITUTE(A2,"~",REPT(" ",999)),1+(ROW($1:$999)-1)*999,999))&"0")+0) {array formula needs to be entered with Ctrl+Shift+Enter} Regards,

Hi, What is your expected outcome for 105514 if it is not 10:55:14? Regards,

Hi, Other than above, you can use Data Validation to restrict decimals with following: Select your cell(s) Go to Data > Data Validation > Custom: =INT(A1)=A1 Regards,

Hi, Based on your data structure, if your date is YYYYMMDD and time is HHMMSS, this can be used: =DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))+TIME(LEFT(E2,2),MID(E2,3,2),RIGHT(E2,2)) Regards,