Remove trailing spaces in Q2:Q4,
Then,
In L2, formula copied down :
=SUMPRODUCT(SUMIF(Q:Q,B2:K2,T:T))
Posts by bosco_yip
-
-
So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.
2 options,
1] You can use a Textjoin UDF ( using Google to search)
2] Used a longer formula or helper columns to obtain the target.
Regards
-
Using Textjoin function for the above formula :
1] Excel 2019, required CSE entry.
2] Office 365 with dynamic functions subscribed, CSE entry doesn't required
3] Office 365 without dynamic functions subscribed, required CSE entry.
-
Try,
In G2, array formula (Ctrl+Shift+Enter) copied down :
=TEXTJOIN(", ",,IF(ISNUMBER(SEARCH(F2&CHAR(10),B$2:B$10&CHAR(10))),A$2:A$10,""))
-
To convert a Gregorian date to a Chinese date
In B3, enter formula :
=TEXT(A3,"[$-130000]d mmm e")
-
Display More
Maybe,
In B2, CSE formula (Ctrl+Shift+Enter) copied down :
=MAX(FREQUENCY(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0),ROW(INDIRECT("1:"&LEN(A2)))))>=3
Or,
it can be shortened to :
=MAX(FREQUENCY(0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0+MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2)))>=3
Regards
-
Maybe,
In B2, CSE formula (Ctrl+Shift+Enter) copied down :
=MAX(FREQUENCY(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),2),0),ROW(INDIRECT("1:"&LEN(A2)))))>=3
-
Thank u it worked but can u explain to me
To understand how does the two formula worked ?
Try to use the "Evaluate Formula" feature, that resides on the Formulas tab, in the "Formula Auditing" group.
All you need to do is click the Evaluate button and examine the value of the underlined formula part.
The result of the most recent evaluation appears in italics.
Continue clicking the Evaluate button until each part of your formula has been tested.
Regards
-
1] Change B2, data validation formula into :
=OFFSET(C9,MATCH(A2,B10:B22,0),,COUNTIF(B10:B22,A2))
2] In C2, formula copied across right to E2
=VLOOKUP($A2&$B2,Table1[#All],MATCH(C1,Table1[#Headers],0),0)
-
Maybe,
In I6 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET($A$4,MATCH($H6,Company,0),2,,2)
-
Return the last row of text and remove the starting 5 words, formula solution,
In B2 enter formula :
=TRIM(MID(SUBSTITUTE(" "&MID(A2,1-LOOKUP(1,-FIND(CHAR(10),A2,ROW(A:A))),599)," ",REPT(" ",49)),299,599))
-
Another option using Sum + Offset function
In D4, formula copied down :
=SUM(OFFSET(F$3,MATCH(B4,$F$4:$F$9,0),MONTH(C4)+(DAY(C4)>10),1,13+(DAY(C4)>10)-MONTH(C4)))
Remark :
It is better to add ….MATCH(B4,$F$4:$F$9,0)… inside the Offset formula to find the Row position of the Company name in the source list
Regards
-
In C35, formula copied across and down :
=SUMPRODUCT(-TEXT(LEFT(TEXT(MID(C$3:C$33&"@",FIND($A35&"-",C$3:C$33&$A35&"-")+1,COLUMN($A:$I)),),COLUMN($A:$I)-1),"[<>];;;\0"))
-
In J3, array formula (CSE) copied across right to M3 and all copied down :
=IFERROR(TEXTJOIN("/",,IF($C3:$I3=J$2,$C$2:$I$2,"")),"")
-
Maybe,
In C2, formula copied down :
=LOOKUP(B2,OFFSET(INDEX($I:$I,MATCH(A2,$G:$G,0)),0,0,4,3))
-
Or try this non-array formula
In A1, formula copied down :
=IFERROR(LOOKUP(1,SEARCH(L$1:L$4,B1),M$1:M$4),"")
Regards
-
Try,
In H4, enter formula :
=AGGREGATE(15,6,H9:H30/(D9:D30=D4)/(E9:E30=E4)/(F9:F30=F4)/(G9:G30=G4)/(H9:H30>=C4),1)
Regards
-
Maybe try this formula solution
In "SUMMARY" sheet cell A100, enter formula and copied across to F100 :
=IFERROR(INDEX(INVOICES!A:A,AGGREGATE(15,6,ROW(INVOICES!$A$4:$A$40)/(INVOICES!$F$4:$F$40<>""),ROW(A1))),"")
And,
Select cell C100:F100 >> Custom Cell Format, enter : #,##0.00;;;
Then,
Select A100:F100, all copied down to row line no. 136
>> Finish
-
In D8 formula copied down :
=(1+IFERROR(VLOOKUP(VLOOKUP(MONTH(C8),$H$8:$I$19,2,0) & "-" & YEAR(C8),$A$2:$B$61,2,0),B$2))^(1/12)
Regards
-
Extract top 4 from the last 15 in the list (new values will be added to the end of the list)
Assume data in A2:A?
In C2, enter formula and copied down 3 lines :
=LARGE(OFFSET(A$1,MATCH(9^9,A:A)-1,0,-15),ROW(A1))