# Posts by [email protected]

bosco_yip

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 &quot;.&quot; as separator (help)

bong25

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:

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

## Files

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)*100

But, 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

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.