Get Digital Help
  • Home
  • Functions
  • Formulas
  • Products
  • Archive
  • Contact
  • About Me
    • About me
    • Blogs I read

    Compatibility

    CONFIDENCE functionCalculates the confidence interval for a population mean, using a normal distribution.

    FLOOR functionRounds a number down, toward zero, to the nearest multiple of significance.

    MODE functionCalculates the most frequent number in a cell range.

    RANK functionCalculates the rank of a specific number compared to a list of numbers.

    STDEV functionCalculates the standard deviation of a group of values.

    Database

    DAVERAGE functionCalculates an average based on values in a list or database that meet specific conditions.

    DCOUNT functionCounts cells containing numbers and that meet a condition or criteria.

    DCOUNTA functionCounts nonempty cells in a column you specify, in a database where records also meet a condition or criteria.

    DGET functionFetches a value from a column in a database whose records meet a condition or criteria.

    DMAX functionExtracts the maximum number from a column in a database whose records match a condition or criteria.

    DMIN functionExtracts the smallest number from a column in a database whose records match a condition or criteria.

    DPRODUCT functionMultiplies numbers that match a condition or criteria in a database.

    DSTDEV functionCalculates an estimation of the standard deviation based on a sample of a population. The function also allows you to specify criteria applied to a database.

    DSTDEVP functionCalculates the standard deviation based on a population. The function also allows you to specify criteria applied to a database.

    DSUM functionAdds numbers in a database/list that meets a condition or criteria.

    DVARP functionReturns the variance of an entire population. The numbers are in a column of records in a dataset or database that meets a given condition or criteria.

    Date and Time

    DATE functionReturns a number that acts as a date in the Excel environment.

    DATEDIF functionReturns the number of days, months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.

    DATEVALUE functionReturns an Excel date value (serial number) based on a date stored as text.

    DAY functionExtracts the day as a number from an Excel date.

    DAYS functionCalculates the number of days between two dates.

    EDATE functionReturns a date determined by a start date and a number representing how many months.

    EOMONTH functionReturns an Excel date for the last day of a given month using a number and a start date.

    HOUR functionReturns an integer representing the hour of an Excel time value.

    MINUTE functionReturns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 to 59.

    MONTH functionExtracts the month as a number from an Excel date.

    NETWORKDAYS functionReturns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.

    NOW functionReturns the current date and time.

    SECOND functionReturns an integer representing the second based on an Excel time value

    TIME functionReturns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.

    TIMEVALUE functionReturns a decimal number based on a text string.

    TODAY functionReturns the Excel date (serial number) of the current date.

    WEEKDAY functionConverts a date to a weekday number from 1 to 7.

    WORKDAY functionReturns a date based on a start date and a given number of working days (nonweekend and nonholidays).

    YEAR functionConverts a date to a number representing the year in the date.

    YEARFRAC functionReturns the fraction of the year based on the number of whole days between a start date and an end date.

    Engineering

    BIN2DEC functionConverts a binary number to the decimal number system.

    BIN2HEX functionConverts a binary number to hexadecimal.

    BIN2OCT functionConverts a binary number to octal.

    BITAND functionCalculates a bitwise 'AND' of two numbers.

    BITLSHIFT functionCalculates a number whose binary representation is shifted left by a specified number of bits.

    BITOR functionPerforms a bitwise 'OR' of two numbers.

    BITRSHIFT functionCalculates the number where the binary equivalent is shifted right by a specified number of bits and then converted back to a number.

    BITXOR functionCalculates a decimal number that is a result of a bitwise comparison "XOR" of two numbers.

    COMPLEX functionReturns a complex number based on a real and imaginary number.

    CONVERT functionConverts a number from one measurement system to another.

    DEC2BIN functionConverts a decimal number to a binary number.

    DEC2HEX functionConverts a decimal number to a hexadecimal number.

    DEC2OCT functionConverts a decimal number to an octal number.

    Financial

    ACCRINT functionCalculates the accrued interest for a security that pays periodic interest.

    ACCRINTM functionCalculates the accrued interest for a security that pays interest at maturity.

    CUMIPMT functionCalculates the accumulated interest based on a start and end period on a loan.

    CUMPRINC functionCalculates the accumulated principal based on a start and end period on a loan.

    DB functionCalculates the depreciation of an asset for a given period using the fixed-declining balance method.

    DOLLARFR functionConverts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars.

    EFFECT functionCalculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.

    FV functionReturns the future value of an investment based on a constant interest rate.

    IPMT functionCalculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.

    ISPMT functionCalculates the interest paid during a specific period of an investment.

    NOMINAL functionCalculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.

    PDURATION functionCalculates how many periods required by an investment to reach a given amount based on a rate in percentage.

    PMT functionReturns the payment needed for borrowing a fixed sum of money based on constant payments and interest rate.

    PPMT functionCalculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.

    PRICEMAT functionCalculates the price per $100 nominal value of a bond that pays interest at maturity.

    RATE functionReturns the interest rate per period of an annuity.

    Information

    CELL functionGets information about the formatting, location, or the contents of a cell.

    ERROR.TYPE functionReturns a number that represents one of the error values in Excel.

    INFO functionReturns information about the current operating environment, file path, number of active worksheets, Excel version etc.

    ISBLANK functionReturns TRUE if the argument is an empty cell, returns FALSE if not.

    ISERR functionReturns TRUE if a cell returns an error, except error value #N/A.

    ISERROR functionReturns TRUE if a cell contains an error.

    ISFORMULA functionReturns TRUE if a cell contains a formula, FALSE if text, number or boolean value.

    ISLOGICAL functionReturns TRUE if value is boolean. A boolean value is either TRUE or FALSE.

    ISNA functionReturns TRUE if value is a #N/A error.

    ISNONTEXT functionReturns TRUE if value is not text, also returns TRUE if cell is empty.

    ISNUMBER functionChecks if a value is a number, returns TRUE or FALSE.

    ISODD functionReturns TRUE if a cell contains an odd number, FALSE if even number.

    ISREF functionReturns TRUE if value refers to a reference.

    ISTEXT functionReturns TRUE if argument is text.

    N functionReturns a value converted into a number.

    NA functionReturns the error value #N/A meaning "value is not available".

    SHEET functionReturns the sheet number of the cell reference sheet.

    SHEETS functionReturns the number of sheets in a reference.

    TYPE functionUse TYPE to find out what type of data is returned by a function or formula.

    Logical

    AND functionPerform a logical test in each argument and if all arguments return TRUE the AND function returns TRUE.

    FALSE functionReturns the logical (boolean) value FALSE.

    IF functionReturns one value if the logical test is TRUE and another value if the logical test is FALSE.

    IFERROR functionIf the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

    IFNA functionHandles #N/A errors only, it returns a specific value if the formula returns a #N/A error.

    IFS functionChecks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.

    NOT functionReturns the boolean opposite to the given argument.

    OR functionEvaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.

    SWITCH functionReturns a given value determined by an expression and a list of values.

    TRUE functionReturns the logical (boolean) value TRUE.

    Lookup and reference

    ADDRESS functionReturns the address of a specific cell, you need to provide a row and column number.

    AREAS functionReturns the number of cell ranges and single cells in a reference.

    CHOOSE functionGets a value based on a number.

    COLUMN functionReturns the column number of the top-left cell of a cell reference.

    COLUMNS functionCalculates the number of columns in a cell range.

    FORMULATEXT functionReturns a formula as a text string.

    HLOOKUP functionSearches the top row in a data range for a value and return another value on the same column in a row you specify.

    HYPERLINK functionBuilds a link in a cell.

    INDEX functionReturns a value from a cell range, you specify which value based on a row and column number.

    INDIRECT functionReturns the cell reference based on a text string and shows the content of that cell reference.

    LOOKUP functionFind a value in a cell range and return a corresponding value on the same row.

    MATCH functionReturns the relative position of an item in an array that matches a specified value in a specific order.

    OFFSET functionReturns a reference to a range that is a given number of rows and columns from a given reference.

    ROW functionCalculates the row number of a cell reference.

    ROWS functionCalculate the number of rows in a cell range.

    TRANSPOSE functionConverts a vertical range to a horizontal range, or vice versa.

    VLOOKUP functionLets you search the leftmost column for a value and return another value on the same row in a column you specify.

    Math and trigonometry

    ABS functionConverts negative numbers to positive numbers.

    ACOS functionCalculates the arccosine, or inverse cosine, of a number.

    ACOSH functionCalculates the inverse hyperbolic cosine of a number.

    ACOT functionCalculates the inverse cotangent of a number.

    ACOTH functionCalculates the inverse hyperbolic cotangent of a number.

    AGGREGATE functionPerform different specific functions to a list or database.

    ASIN functionCalculates the arcsine of a number.

    ASINH functionCalculates the inverse hyperbolic sine of a number.

    ATAN functionCalculates the arctangent of a number.

    ATAN2 functionCalculates the arctangent of an angle using specific x- and y-coordinates.

    ATANH functionCalculates the inverse hyperbolic tangent of a number.

    BASE functionConverts a number into a text representation with a given radix (base).

    CEILING functionRounds a number up to its nearest multiple.

    COMBIN functionReturns the number of combinations for a specific number of elements out of a larger group.

    COMBINA functionCalculates the number of combinations for a given number of elements from a larger group of elements.

    COS functionCalculates the cosine of an angle.

    COSH functionCalculates the hyperbolic cosine of a number.

    COT functionCalculates the cotangent of an angle specified in radians.

    CSC functionCalculates the cosecant of an angle (radians).

    DECIMAL functionConverts a text representation of a number in a given base into a decimal number.

    DEGREES functionCalculates degrees from radians.

    EVEN functionRounds a number up to the nearest even whole number.

    EXP functionReturns e raised to the power of a number, e equals 2.71828182845904.

    FACT functionReturns the factorial of a number.

    FACTDOUBLE functionReturns the double factorial of a number.

    FLOOR.MATH functionRounds a number down to the nearest integer or to the nearest multiple of significance.

    GCD functionCalculates the greatest common divisor that divides all given arguments without a remainder.

    INT functionRemoves the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.

    LCM functionCalculates the least common multiple. The least common multiple is the smallest positive integer that is a multiple of all integer arguments. Use the LCM function to find fractions with different denominators.

    LN functionCalculates the natural logarithm of a number. Natural logarithms are based on the constant e.

    LOG functionCalculates the logarithm of a number to a specific base.

    MINVERSE functionCalculates the inverse matrix for a given array.

    MMULT functionCalculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

    MOD functionReturns the remainder after a number is divided by divisor.

    MROUND functionCalculates a number rounded to a given multiple.

    MULTINOMIAL functionCalculates the ratio of the factorial of a sum of values to the product of factorials.

    MUNIT functionCalculates the identity matrix for a given dimension

    PI functionReturns the number pi (¶).

    POWER functionCalculates a number raised to a power.

    PRODUCT functionReturns the product of the numbers given in the argument.

    QUOTIENT functionReturns the integer portion of a division.

    RADIANS functionConverts degrees to radians.

    RAND functionCalculates a random real number greater than or equal to 0 and less than 1.

    RANDBETWEEN functionReturns a random whole number between the numbers you specify.

    SEC functionCalculates the secant of an angle.

    SECH functionCalculates the hyperbolic secant of an angle.

    SERIESSUM functionCalculates the sum of a power series based on a formula.

    SIGN functionReturns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.

    SIN functionCalculates the sine of an angle.

    SINH functionCalculates the hyperbolic sine of a number.

    SQRT functionCalculates the positive square root.

    SUBTOTAL functionReturns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.

    SUM functionAllows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

    SUMIF functionSums numerical values based on a condition.

    SUMIFS functionAdds numbers based on criteria.

    SUMPRODUCT functionCalculates the product of corresponding values and then returns the sum of each multiplication.

    SUMSQ functionCalculates the sum of the squares of the arguments.

    SUMX2MY2 functionCalculates the sum of the difference of squares of corresponding values in two arrays.

    SUMX2PY2 functionCalculates the sum of the sum of squares of corresponding values in two arrays.

    SUMXMY2 functionCalculates the sum of squares of differences of corresponding values in two arrays.

    TAN functionCalculates the tangent of an angle.

    TANH functionCalculates the hyperbolic tangent of a number.

    TRUNC functionRemoves the fractional part of the number to an integer.

    Statistical

    AVEDEV functionCalculates the average of the absolute deviations of data points from their mean.

    AVERAGE functionCalculates the average of numbers in a cell range.

    AVERAGEA functionReturns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.

    AVERAGEIF functionReturns the average of cell values that are valid for a given condition.

    AVERAGEIFS functionReturns the average of cell values that evaluates to TRUE for multiple criteria.

    CORREL functionCalculates the correlation between two groups of numbers.

    COUNT functionCounts all numerical values in an argument.

    COUNTA functionCounts the non-empty or blank cells in a cell range.

    COUNTBLANK functionCounts empty or blank cells in a range.

    COUNTIF functionCalculates the number of cells that is equal to a condition.

    COUNTIFS functionCalculates the number of cells across multiple ranges that equals all given conditions.

    EXPON.DIST functionReturns the exponential distribution. Use EXPON.DIST to model the time between events.

    FORECAST.LINEAR functionCalculates a value based on existing x and y values using linear regression.

    FREQUENCY functionCalculates how often values occur within a range of values and then returns a vertical array of numbers.

    GAMMA functionCalculates the GAMMA value.

    GEOMEAN functionCalculates the geometric mean.

    GROWTH functionReturns estimated exponential growth based on given data.

    LARGE functionCalculates the k-th largest value from an array of numbers.

    MAX functionCalculate the largest number in a cell range.

    MAXIFS functionCalculates the highest value based on a condition or criteria.

    MEDIAN functionCalculates the median based on a group of numbers. The median is the middle number of a group of numbers.

    MIN functionReturns the smallest number in a cell range.

    MINA functionReturns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).

    MINIFS functionCalculates the smallest value based on a given set of criteria.

    MODEMULT functionReturns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.

    NORM.DIST functionCalculates the normal distribution for a given mean and standard deviation.

    NORM.INV functionCalculates the inverse of the normal cumulative distribution for a given mean and standard deviation.

    PERMUT functionReturns the number of permutations for a set of elements that can be selected from a larger number of elements.

    PERMUTATIONA functionReturns the number of permutations for a specific number of elements that can be selected from a larger group of elements.

    PHI functionCalculates a number of the density function for a standard normal distribution.

    PROB function

    QUARTILE.INC functionReturns the quartile of a data set, based on percentile values from 0..1, inclusive.

    RANK.AVG functionReturns the rank of a number out of a list of numbers.

    SKEW functionCalculates the skewness of a group of values.

    SLOPE functionCalculates the slope of the linear regression line through coordinates.

    SMALL functionReturns the k-th smallest value from a group of numbers.

    STANDARDIZE functionCalculates a normalized value from a distribution characterized by mean and standard_dev.

    STDEV.P functionReturns standard deviation based on the entire population.

    STDEV.S functionReturns standard deviation based on a sample of the entire population.

    STDEVA functionEstimates the standard deviation from a sample of values.

    STDEVPA functionReturns the standard deviation based on the entire population, including text and logical values.

    TREND functionCalculates values along a linear trend.

    TRIMMEAN functionCalculates the mean of the interior of a data set.

    VAR.P functionReturns the variance based on the entire population. The function ignores logical and text values.

    VAR.S functionThe VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.

    Text

    ASC functionConverts full-width (double-byte) characters to half-width (single-byte) characters.

    CHAR functionConverts a number to the corresponding ANSI character determined by your computers character set.

    CLEAN functionDeletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.

    CODE functionReturns the corresponding number for the first character based on your computers character set. (PC- ANSI)

    CONCAT functionConcatenates values from multiple cells.

    CONCATENATE functionJoins text strings.

    EXACT functionChecks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.

    FIND functionReturns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

    FIXED functionRounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

    LEFT functionExtracts a specific number of characters always starting from the left.

    LEN functionReturns the number of characters in a cell value.

    LOWER functionConverts a value to lower case letters.

    MID functionReturns a substring from a string based on the starting position and the number of characters you want to extract.

    REPT functionRepeats a specific text a chosen number of times.

    RIGHT functionExtracts a specific number of characters always starting from the right.

    SEARCH functionReturns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

    SUBSTITUTE functionReplaces a specific text string in a value. Case sensitive.

    T functionReturns a text value if the argument is a text value.

    TEXT functionConverts a value to text in a specific number format.

    TEXTJOIN functionCombine text strings from multiple cell ranges.

    TRIM functionDeletes all blanks or space characters except single blanks between words in a cell value.

    UPPER functionConverts a value to upper case letters.

    VALUE functionConverts a text string that represents a number to a number.

    Web

    ENCODEURL functionReturns a URL-encoded string.

    WEBSERVICE functionGets data from a web service on the Internet or Intranet.




How to use the ERROR.TYPE function

The ERROR.TYPE function returns a number that represents one of the error values in Excel. Formula in cell C3: =ERROR.TYPE(B3) […]

If cell contains any text

The picture above shows different values in column B and a formula in column C that tries to identifies the […]

Count cells with text

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, […]

How to use the NA function

The NA() function returns the error value #N/A meaning "value is not available". Formula in cell B3: =NA() Excel Function […]

How to use the TYPE 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 […]

How to use the SHEET function

The SHEET function returns the sheet number of the cell reference sheet. Formula in cell D3: =SHEET(A1) Excel Function Syntax […]

How to use the N function

The N function returns a value converted into a number. Formula in cell C3: =N(B3) The N function returns the […]

How to use the INFO function

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 […]

How to use the ISODD function

The ISODD function returns TRUE if a cell contains an odd number, FALSE if even number. Formula in cell C6: […]

How to use the ISERR function

The ISERR function returns TRUE if a cell returns an error, except error value #N/A. Formula in cell C6: =ISERR(B6) […]

How to use the ISNA function

The ISNA function returns TRUE if value is a #N/A error. Formula in cell C3: =ISNA(B3) Excel Function Syntax ISNA(value) […]

How to use the ISREF function

The ISREF function returns TRUE if value refers to a reference. Formula in cell C3: =ISREF(B3) Excel Function Syntax ISREF(value) […]

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) […]

How to use the CELL function

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 […]

SUMPRODUCT if not blank

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 […]

If cell contains text

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: […]

Highlight records [AND logic]

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 […]

Sum values containing text based on a condition

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 […]

Merge two columns

  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 […]

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: The formula in cell F3 gets only […]

Remove blank cells

In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]

      Formula categories

      Count Dates Extract Index and Match Logic Lookups String manipulation Sum Time Vlookup
  • Welcome! I am Oscar and here to help you!

    Feel free to comment and ask Excel questions.

    Don´t forget to add my RSS feed and subscribe to new blog articles by email.

  • Free Blog Emails

  • Search website