Search results

1] Please refer to your fiscal quarter formula : ="Q"&CHOOSE(MONTH($H$2),4,4,4,1,1,1,2,2,2,3,3,3) 2] It can be shorter by : ="Q"&QUOTIENT(MOD(MONTH(H2)-4,12),3)+1 or ="Q"&MID(444111222333,MONTH(H2),1)

Another option for all Excel versions =SUMPRODUCT(C4:INDEX(C7:H7,MATCH(B1,C3:H3,0))*(B4:B7=C12)) or =SUMPRODUCT(OFFSET(C4,,,4,MATCH(B1,C3:H3,0))*(B4:B7=C12))

Or, =SUM(B3:Q3,-SMALL(B3:Q3,{1,2}))

1] Duplicate post >> https://chandoo.org/forum/threads/how-to-use-conditional-formatting-with-parameters.53869/ 2] Please stick on the original post 3] This post is closed.

Another option. Used COLUMN(A1)-1 to generate a series of number 0,1,2,3.....8,9 Then, In D2, enter formula copied across right and down: =LEN($B2)-LEN(SUBSTITUTE($B2,COLUMN(A$1)-1,"")) or this shortened way. =LEN($B2)-LEN(SUBSTITUTE($B2,COLUMN(A$1)-1,))

1] I think to use a ROUND function with digit 0, will round up to the nearest whole number. And will meet with you need. Because Excel Formula use mathematic round, VBA use banker round. So, 2[ In C2, formula copied down : =ROUND(A$12/A2,0) 3] In D2, formula copied down : =A2*C2

Try, In Q2, formula copied down: =COUNTIFS(F:F,Q$1,G:G,P2) Or, you can use "used range" to save some memory, and become like that: =COUNTIFS(F$2:F$187,Q$1,G$2:G$187,P2)

@Standing bear, Hi to you, and welcome to the board. 1} I know you are not new to us since you have registered to us in June 2021 for 2 years of time. 2] But I still attached our forum rule to you for read. https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/ 3] Please...

Try In Cell L3 formula copied down: =IF(F3="","",IFERROR(LOOKUP(1,0/(A$3:A$585=K3)/(B$3:B$585=F3),C$3:C$585),"")) or =IF(F3="","",SUMIFS(C:C,A:A,K3,B:B,F3))

Sorry, No Press Ctrl+H >> Select B5 >> Put the cursor in the right side of last character, then press mouse left key and move right, press Ctrl+C >> in "Find what" then click Ctrl+V >>"Replace with", keep blank>> press "Replace All" >> OK Good luck

Thanks Debaser, I just used CODE function to check and returned CHAR(63) in start and end Regards

Try, 1] Your "Sheet AAAAA" have Char(63) found at the head & tail in range B5:B67 2] Select B5 >> copy the character after the last visible character >> Ctrl+"H" >> paste to "Find what" do not enter anything in the "Replace with" >> OK (Please refer to below screenshot replacements result)...

9^9 is a lazy form (short form) of Excel Bignum 9.9999999999999999E+307, please see herein details as below link: https://www.mrexcel.com/board/threads/9-9999999.102091/ Regards

If you have XLOOKUP function, the easiest way to return a column on the left of the column of research is : 1. Select the value to research 2. Select the column of research (only one column to select) 3. Select the column to return whatever if it's on the right or the left of the research's...

To use VLOOKUP to perform a lookup to the left, you can use the CHOOSE function to reorder the lookup table. For example: =VLOOKUP(E5,CHOOSE({1,2},score,rating),2,0)

Try, 1] In Sheet "Description (table2) ", revised B4 array (CSE) formula copied down to B100 Remark : Just add a &"" as per highlighted below. {=IFERROR(INDEX(Tabela1[Description],MATCH(0,COUNTIF($B$3:B3,Tabela1[Description]&""),0)),"")} >> become >> 2] Add new description in table2, cell...

Maybe Try this old formula, without Weekday or Workday function =TODAY()-SUM((MOD(TODAY(),7)+1={1,2})*{1,2}) or =TODAY()-MID(1200000,MOD(TODAY(),7)+1,1)

Maybe, =sum(countifs($B$6:$B$3700,{"new","open"},$H$6:$H$3700,"",$O$6:$O3700,">=30",$O$6:$O$3700,"<=90"))

Try, 1] In B2, formula copied down: =IF($A2="","",INDIRECT($A2&"!N29")) 2] In C2, formula copied down: =IF($A2="","",INDIRECT($A2&"!O29"))

Duplicated post https://chandoo.org/forum/threads/improve-spreadsheet-design.53433/

Or, =SUM(((A6:A2600="NEW")+(A6:A2600="OPEN"))*(C6:C2600<=90)*(C6:C2600>=30))

Or, =REPT(CHAR(149),(H17-A17>9)+(H17>0))

A lot of way, why not just >> In E3, enter formula then copied across right and down, and will obtain the same result : =Sheet1!E3+Sheet2!E3

Or, Try to use Sumif function instead In B4, formula copied across and down: =SUMIF(Data!$D:$D,B$2,INDEX(Data!$1:$1,MATCH($A4,Data!$6:$6,0)))

Try, A4, formula copied down: =IF(((B4<B1)+(B4>B2))*((C4<C1)+(C4>C2)),"Fail","Pass")