So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.
Posts by [email protected]
-
-
Wasn't the TEXTJOIN a new function with office 365 and excel 2019, so there will be no CSE. If the poster has a previous version he will not have TEXTJOIN. I stand corrected if need be.
-
Re: Last Time Data was Entered Formula
Hi
If that data is in A1:C11 including the headers, try the following.
=LOOKUP("zzz",C2:C11,A2:A11)
-
Re: Conditional formatting via formula
Hi
For the conditional formatting formula, try the following.
=--LEFT(A1,FIND(" ",A1)-1)<1
-
Re: Finding the maximum value in a range that is between two values
If you have Excel 2010>, then you could use aggregate function.
=AGGREGATE(14,6,B7:B34/((C7:C34>=C2)*(C7:C34<=E2)),1)
-
Re: Count Unique Cell Values
Hi cdemaria
For a formula approach:
Assuming your data is in A1:A10 including a column header. In say C2: =IFERROR(INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF(C$1:C1,$A$2:$A$10),0,0),0)),"") & copy down. For excel versions before 2007, replace the IFERROR with IF & ISERROR.
Then in D2 & copy down: =COUNTIF($A$2:$A$10,C2) -
Re: convert and calculate time entered with period "." as separator (help)
Use Auto Correct > Replace: "." With: ":" Change it back afterwards.
Or enter your times as eg: 12.15 > Find and Replace > Find "." Replace ":" > Replace all.
Then use something like this:
=SUM(MOD(H9-G9,1),MOD(J9-I9,1),MOD(L9-K9,1),MOD(N9-M9,1),MOD(P9-O9,1),MOD(R9-Q9,1),MOD(T9-S9,1),MOD(V9-U9,1),MOD(X9-W9,1),MOD(Z9-Y9,1),MOD(AB9-AA9,1),MOD(AD9-AC9,1),MOD(AF9-AE9,1),MOD(AH9-AG9,1)) -
Re: convert date dd/mm/yyyy hh:mm into mm/dd/yyyy
Try in D1: =TRIM(A2) & copy down, Copy the column paste values back in column A. Then Text to Columns > Delimited > Next > Next > Select Date & MDY from the drop down box > Finish. Format as "mm/dd/yyyy"
-
Re: LOOKUP function returning incorrect result.
Also ensure correct spelling!
-
Re: Remove blanks from dropdown list
Hi Kiffar
See the attached file. Columns N, O, P have the formulas, which you can put wherever you require. I have grouped them for this exercise. Then I have defined a name for the list called DataValList.
Kevin -
Re: Sum product with long string
Hi AVB XL
Both of these seem OK.=SUMPRODUCT(--($C$2:$C$3474=$B$3),--($O$2:$O$3474="Dialed into IVR, Prompted for number, Unmatched, Hung Up"))
Or a COUNTIFS if you have Excel 2007>
=COUNTIFS($C$2:$C$3474,$B$3,$O$2:$O$3474,"Dialed into IVR, Prompted for number, Unmatched, Hung Up")
-
Re: Using the Sumproduct function within a certain date range
Hi geobear
You could also SUMIFS as you have Excel 2010. -
Re: Calculating sum of a product of two adjacent rows in one column
Would it not make more sense if you had extra column and put you totals in there. Easier to maintain and for other people.
-
Re: Totals by Day/Month/Year using data from a table
Hi wangtangkiki
You can use the SUMIF, In O3 and copy down.=SUMIF($C$3:$C$44,L3,$J$3:$J$44)
-
Re: Avoid duplicates in COUNTIF function where counting A and A*
Hi Mark44
Try the following:
=SUM(COUNTIF(L6:L27,{"A","A~*","B","C"}))/COUNTA(L6:L27)*100But, you get 100 all the time!
-
Re: Return the count of cells within a column which are between 2 date ranges
Hi
If you have excel 2007 >, use COUNTIFS, assuming your data is in column A;
=COUNTIFS(A1:A100,">=01/10/2012",A1:A100,"<=31/12/2012")
Or
SUMPRODUCT: =SUMPRODUCT(--(A1:A100>=DATE(2012,12,1))*(A1:A100<=DATE(2012,12,31))) -
Re: Sumif with 2 criteria matching month
Hi skamat
Alternatively use the TEXT function within the SUMPRODUCT, in I17, copy across & down:
=SUMPRODUCT(--(TEXT($D$6:$D$23,"mmm")=I$16),--($C$6:$C$23=$H17),$E$6:$E$23)
-
Re: How can I get the label result from the left using excel function..help pls..
Hi ka_poroy
Perhaps the following, assuming your data is in A1:B6 inc headers.
In B2 and copy down: =LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&1234567890)+1)) -
Re: date of next anniversary
Hi
How about in E7 & copy down:
=DATE(YEAR(C7)+ROUNDUP(D7,-1),MONTH(C7),DAY(C7)) -
Re: SUM every 'N' Rows
Hi
Or a formula version.