# Posts by [email protected]

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

So the answer is, if they do not have office 365 or excel 2019, they can not use the TEXTJOIN function.

• ## Help Inverting a table - list of requirements with associated tests to become a list of tests with associated requirements

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.

• ## Last Time Data was Entered Formula

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)

• ## Conditional formatting via formula

Re: Conditional formatting via formula

Hi

For the conditional formatting formula, try the following.

=--LEFT(A1,FIND(" ",A1)-1)<1

• ## Finding the maximum value in a range that is between two values

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)

• ## Count Unique Cell Values

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)

• ## convert and calculate time entered with period "." as separator (help)

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:

• ## convert date dd/mm/yyyy hh:mm into mm/dd/yyyy

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"

• ## LOOKUP function returning incorrect result.

Re: LOOKUP function returning incorrect result.

Also ensure correct spelling!

• ## Remove blanks from dropdown list

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

• ## Sum product with long string

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")

• ## Using the Sumproduct function within a certain date range

Re: Using the Sumproduct function within a certain date range

Hi geobear

You could also SUMIFS as you have Excel 2010.

• ## Calculating sum of a product of two adjacent rows in one column

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.

• ## Totals by Day/Month/Year using data from a table

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)

• ## Avoid duplicates in COUNTIF function where counting A and A*

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!

• ## Return the count of cells within a column which are between 2 date ranges

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

• ## Sumif with 2 criteria matching month

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)

• ## How can I get the label result from the left using excel function..help pls..

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

• ## date of next anniversary

Re: date of next anniversary

Hi

How about in E7 & copy down:
=DATE(YEAR(C7)+ROUNDUP(D7,-1),MONTH(C7),DAY(C7))

• ## SUM every 'N' Rows

Re: SUM every 'N' Rows

Hi

Or a formula version.