Archive for Information
The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]
The NA() function returns the error value #N/A meaning "value is not available". Formula in cell B3: =NA() Excel Function […]
Use TYPE to find out what type of data is returned by a function or formula. Formula in cell C3: […]
How to use the SHEETS function
The SHEETS function returns the number of sheets in a reference. It returns the number of sheets in a workbook if […]
The SHEET function returns the sheet number of the cell reference sheet. Formula in cell D3: =SHEET(A1) Excel Function Syntax […]
The N function returns a value converted into a number. Formula in cell C3: =N(B3) The N function returns the […]
The INFO function returns information about the current operating environment, file path, number of active worksheets, Excel version etc. Excel […]
How to use the ISFORMULA function
The ISFORMULA function returns TRUE if a cell contains a formula, FALSE if text, number or boolean value. Formula in […]
The ISODD function returns TRUE if a cell contains an odd number, FALSE if even number. Formula in cell C6: […]
The ISERR function returns TRUE if a cell returns an error, except error value #N/A. Formula in cell C6: =ISERR(B6) […]
The ISNA function returns TRUE if value is a #N/A error. Formula in cell C3: =ISNA(B3) Excel Function Syntax ISNA(value) […]
The ISREF function returns TRUE if value refers to a reference. Formula in cell C3: =ISREF(B3) Excel Function Syntax ISREF […]
How to use the ISNONTEXT function
The ISNONTEXT function returns TRUE if value is not text, also returns TRUE if cell is empty. Formula in cell […]
How to use the ISLOGICAL function
The ISLOGICAL function returns TRUE if value is boolean. A boolean value is either TRUE or FALSE. Formula in cell C3: […]
How to use the ISTEXT function
The ISTEXT function returns TRUE if value is text. Formula in cell C3: =ISTEXT(B3) Excel Function Syntax ISTEXT(value) Arguments value […]
How to use the ISERROR function
The ISERROR function returns TRUE if a cell returns an error. Formula in cell D3: =ISERROR(B3) Excel Function Syntax ISERROR(value) […]
How to use the ISBLANK function
The ISBLANK function returns TRUE if the argument is an empty cell, returns FALSE if not. Excel Function Syntax ISBLANK(value) […]
The CELL function gets information about the formatting, location, or the contents of a cell. The formula example above in […]
How to use the ISNUMBER function
The ISNUMBER function checks if a value is a number, returns TRUE or FALSE. Formula in cell D3: =ISNUMBER(B3) Excel […]
The above image demonstrates how to ignore blank cells in a SUMPRODUCT formula. The following formula is shown in cell E3.
INDEX MATCH – multiple results
The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […]
The easiest way to check if a cell contains a specific text string is, in my opinion, the IF and […]
Search for a text string and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Filter unique distinct values, sorted and blanks removed
Table of Contents Filter unique distinct values, sorted and blanks removed Filter duplicate values, sorted and blanks removed EEK asks: […]
This blog post shows you how to create a conditional formatting formula and highlight matching records. You can easily change […]
Create a unique distinct sorted list containing both numbers text removing blanks
Table of contents Create a unique distinct sorted list containing both numbers text removing blanks Create a unique distinct sorted […]
Merge two columns with possible blank cells
Question: This article is terrific. Thanks so much for posting this solution! I do have one question: Let's say my […]
Automate net asset value (NAV) calculation on your stock portfolio
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Return value if it exists more then n times
Students are allowed to sign up to maximum two courses. How do I filter students who have signed up for […]
Filter unique distinct values if value contains specific string [Formula and Advanced Filter]
Table of contents Filter unique distinct values using a "contain" condition Filter unique distinct values using a "contain" condition (Advanced […]
Search for multiple text strings in multiple cells and use in data validation
Question: can you please modify the function (f2:f11) to display the text strings in CELL G.. disregard the ROW NUMBER […]
Extract and sort text cells from a range containing both numerical and text values
Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]
Filter common text values in two different ranges
Array formula in B26: =INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), MATCH(0, ISNONTEXT(INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1)), , 1))+COUNTIF($B$25:B25, INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)>0)*ISTEXT(tbl_1)*NOT(COUNTIF($B$25:B25, […]
Filter text values existing in the first range but not in the 2nd range
Click image to enlarge. Array formula in B26: =INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)=0)*NOT(COUNTIF($B$25:B25, tbl_1))*(ISTEXT(tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1, "")), MATCH(0, ISNONTEXT(INDEX(tbl_1, MIN(IF((COUNTIF(tbl_2, tbl_1)=0)*NOT(COUNTIF($B$25:B25, tbl_1))*(ISTEXT(tbl_1)), ROW(tbl_1)-MIN(ROW(tbl_1))+1, […]
Filter unique text values from a range containing both numerical and text values
Array formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)=1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)=1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+(COUNTIF(tbl, INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, […]
Extract duplicate text values from a range containing both numerical and text values
Array formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, tbl)>1)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+COUNTIF($B$14:B14, INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*(COUNTIF(tbl, […]
Count unique distinct text values in a range
Array formula in D14: =SUM(IF(FREQUENCY(COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")), COUNTIF(tbl, "<"&IF(ISTEXT(tbl), tbl, "")))>0, 1, 0)) + CTRL + SHIFT + ENTER […]
Extract unique distinct text list from a range containing both numerical and text values
Formula in B15: =INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(0, ISNONTEXT(INDEX(tbl, MIN(IF((COUNTIF($B$14:B14,tbl)=0)*ISTEXT(tbl), ROW(tbl)-MIN(ROW(tbl))+1)), , 1))+COUNTIF($B$14:B14,INDEX(tbl, MIN(IF((COUNTIF($B$14:B14, tbl)=0)*ISTEXT(tbl),ROW(tbl)-MIN(ROW(tbl))+1)), , 1)), 0), 1) + CTRL […]
Extract numbers and text from a range sorted from A to Z
This blog post describes how to filter text values and then numbers from a range. Array formula in B15: =INDEX(tbl, […]
Filter numbers or text in a column
Question: I want to extract all numeric values into a new column? Also all text values into another column? Answer: […]
Extract a unique distinct list sorted alphabetically and ignore blanks from a range
This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Search for multiple text strings in multiple cells in excel, part 2
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
The picture above shows how to merge two columns into one list using a formula. If you are looking […]
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]
Sorting numbers and text cells in an descending order also removing blanks
This blog article is one out of five articles on the same subject. Sorting text cells using array formula in […]
Find the largest number in an expanding cell range
Question: I have a list that I add data to every day. I would like to find the largest number […]
Lookup with multiple criteria and return multiple search results
Question: How do I search a list containing First name column and a last name column? I want to search […]
Create a unique distinct list where a corresponding column has text cell values
Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]
Search for multiple text strings in multiple cells
Question: How do I search a list for two text strings and return a list with where both strings occurs? […]
5 easy ways to extract unique distinct values
You have quite a few options to choose from if you are looking for a way to create a unique […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? (See pic below) Answer: Array formula in D2: =INDEX(A1:C1, 1, MIN(IF(ISERROR(SEARCH("@", […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
