Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Count unique text values with criteria
  • How to get last column number in range in Excel
  • Vlookup Examples in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to retrieve first match between two ranges in Excel

Data Analysis Examples

  • Excel Line Chart
  • How To Create Pareto Chart in Excel
  • Everything about Charts in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How To Create Frequency Distribution in Excel

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Excel Data validation don’t exceed total
  • Excel Data validation must not contain
  • Excel Data validation allow uppercase only

Tag: COUNTIF function

COUNTIF function: Description, Usage, Syntax, Examples and Explanation

by

What is COUNTIF function in Excel? COUNTIF function is one of Statistical functions in Microsoft Excel used to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.In its simplest form, COUNTIF says: =COUNTIF(Where do you want to look?, What …

Continue Reading

Basic numeric sort formula in Excel

by

To dynamically sort data that contains only numeric values, you can use a helper column and a formula created with the RANK and COUNTIF functions. Formula =RANK(A1,values)+COUNTIF(exp_rng,A1)-1 Note: this formula is the set-up for a formula that can extract and display data using a predefined sort order in a helper column. One example here. Explanation In the example shown, the …

Continue Reading

Basic text sort formula in Excel

by

To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<=”&A1) Explanation In the example shown, the formula in C5 is: =COUNTIF(countries,”<=”&B5) where “countries” is the named range B4:B13 How this formula works This formula uses the “greater than or equal to” operator with text, something you …

Continue Reading

One way to track attendance using Excel formula

by

This tutorial shows b.asic attendance tracking formula in Excel One way to track attendance is with simple formulas based on the COUNTIF function. Formula =COUNTIF(range,”x”) Explanation In the example shown, the formula in M5 is: =COUNTIF(C5:L5,”x”) How this formula works This formula simply uses COUNTIF with a criteria of “x” (not quotation marks) to count …

Continue Reading

Check if multiple cells have same value in Excel

by

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on …

Continue Reading

Check multiple cells have same value in Excel

by

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. See illustration below: Formula =COUNTIF(range,”<>value”)=0 Explanation  In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This …

Continue Reading

How to test for all values in a range are at least in Excel

by

To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. Formula =NOT(COUNTIF(range,”<65″)) Explanation In the example shown, the formula in G5 is: =NOT(COUNTIF(B5:F5,”<65″)) How this formula works At the core, this formula uses the COUNTIF function to count any …

Continue Reading

How to retrieve first match between two ranges in Excel

by

To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. Formula =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) Explanation In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where “range1” is the named range B5:B8, “range2” is the named range D5:D7. How this formula works In this example the named range …

Continue Reading

How to count specific words in a range in Excel

by

To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions.  Formula =SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,text,””)))/LEN(text)) Explanation In the example shown, the formula in C11 is: =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,””)))/LEN(C2)) Note: The formula on this page counts instances of a word in a …

Continue Reading

Count birthdays by month in Excel

by

This tutorial show how to Count birthdays by month in Excel using the example below. To count the number of birthdays in a list, you can use a formula based on the SUMPRODUCT and MONTH functions. Formula =SUMPRODUCT(–(MONTH(birthday)=number)) Explanation of how this formula works In the example shown, E5 contains this formula: =SUMPRODUCT(–(MONTH(birthday)=D5)) This formula counts …

Continue Reading

Excel Rank without ties Example

by

This tutorials shows how to Rank numbers without  ties  in Excel. To assign rank without ties, you can use a formula based on the RANK and COUNTIF functions. Formula =RANK(A1,range)+COUNTIF(exp_range,A1)-1 Explanation In the example shown, the formula in E5 is: =RANK(C5,points)+COUNTIF($C$5:C5,C5)-1 where “points” is the named range How this formula works This formula breaks ties with …

Continue Reading

Excel Data validation unique values only

by

Reject duplicate data entry in a cell range. To allow only unique values in a given range, you can use data validation with a custom formula based on the COUNTIF function. Formula =COUNTIF(range,A1)<2 Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule …

Continue Reading

Excel Data validation require unique number

by

Set criteria to accept only unique number, ie no duplicate. To allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions. Formula =AND(ISNUMBER(A1),COUNTIF(range,A1)<2) Cell references in data validation formulas are relative to the upper left cell in the range selected …

Continue Reading

Data validation must not exist in list

by

Accept data that are not included in a specific range of records To allow only values that do not exist in a list, you can use data validation with a custom formula based on the COUNTIF function. Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a custom validation …

Continue Reading

Excel Data validation must begin with

by

Using the example below, this tutorial shows how to create Data validation must begin with in Excel. Formula =EXACT(LEFT(A1,3),”XX-“) Explanation To allow only values that begin with certain text, you can use data validation with a custom formula based on the EXACT and LEFT functions. In the example shown, the data validation applied to C5:C9 is: =EXACT(LEFT(C5,3),”MX-“) …

Continue Reading

Excel Data validation exists in list

by

Using the example below, this tutorial shows how to create Data validation exists in list in Excel. Formula =COUNTIF(list,A1)>0 Explanation Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a your own validation rule for lists when you don’t want the dropdown behavior. To allow only values from a …

Continue Reading

Highlight unique values in Excel

by

This tutorial shows how to Highlight unique values in Excel using the example below; Formula =COUNTIF(data,A1)=1 Explanation Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight unique values. However, if you want more flexibility, you can highlight unique values with your own formula, as explained in this article. …

Continue Reading

Highlight duplicate rows in Excel

by

This tutorial shows how to Highlight duplicate rows in Excel using the example below; Formula =COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1) Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you’ll need to use your own formula, as explained …

Continue Reading

Highlight duplicate values in Excel

by

This tutorial shows how to Highlight duplicate values in Excel using the example below; Formula =COUNTIF(data,A1)>1 Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight duplicate values. However, if you want more flexibility, you can highlight duplicates with your own formula, as explained in this article. …

Continue Reading

Highlight missing values in Excel

by

This tutorial shows how to Highlight missing values in Excel using the example below; Formula =COUNTIF(list,A1)=0 Explanation To compare lists and highlight values that exist in one but not the other,  you can apply conditional formatting with a formula based on the COUNTIF function. For example, to highlight values A1:A10 that don’t exist C1:C10, select A1:A10 and …

Continue Reading

Highlight cells that end with in Excel

by

This tutorial shows how to Highlight cells that end with in Excel using the example below; Formula =COUNTIF(A1,”*text”) Explanation Note: Excel contains many built-in rules for highlighting values with conditional formatting, including a rule to highlight cells that end with specific text. However, if you want more flexibility, you can use your own formula, as explained …

Continue Reading

Find duplicate values in two columns in Excel

by

This tutorial shows how to Find duplicate values in two columns in Excel using the example below; Formula =AND(COUNTIF(range1,A1),COUNTIF(range2,A1)) Explanation To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions. In the example shown, the formula used to highlight duplicate values …

Continue Reading

IF with wildcards in Excel

by

This tutorial shows how to calculate IF with wildcards in Excel using the example below; Formula =IF(COUNTIF(A1,”??-????-???”),””,”invalid”) Explanation The IF function doesn’t support wildcards, but you can combine IF with COUNTIF or COUNTIF to get basic wildcard functionality. In the example shown, the formula in C5 is: =IF(COUNTIF(B5,”??-????-???”),””,”invalid”) How this formula works Unlike several other frequently used functions, …

Continue Reading

if cell contains this or that in Excel

by

This tutorial shows how to calculate if cell contains this or that in Excel using the example below; Formula =IF(SUM(COUNTIF(B5,{“*text1*”,”*text2*”})),”x”,””) Explanation To check to see if a cell contains more than one substring, you can use a formula based on the COUNTIF function. In the example shown, the formula in C5 is: =IF(SUM(COUNTIF(B5,{“*abc*”,”*aaa*”})),”x”,””) How this formula works …

Continue Reading

If cell begins with x, y, or z in Excel

by

This tutorial shows how to calculate If cell begins with x, y, or z in Excel using the example below; Formula =SUM(COUNTIF(A1,{“x*”,”y*”,”z*”}))>0 Explanation To test values to see if they begin with one of several characters (i.e. begin with x, y, or z) , you can use the COUNTIF function together with the SUM function. In the …

Continue Reading

Get nth match with VLOOKUP in Excel

by

This tutorial shows how to Get nth match with VLOOKUP in Excel  using the example below; Formula =VLOOKUP(id_formula,table,4,0) Explanation To get the nth MATCH with VLOOKUP, you’ll need to add a helper column to your table that constructs a unique id that includes the count. If this isn’t practical, you can use an array formula based …

Continue Reading

Find missing values in Excel

by

This tutorial shows how to Find missing values in Excel using the example below; Formula =IF(COUNTIF(list,value),”OK”,”Missing”) Explanation If you want to find out what values in one list are missing from another list, you can use a  simple formula based on the COUNTIF function. The COUNTIF function counts cells that meet supplied criteria, returning the …

Continue Reading

Extract all partial matches in Excel

by

This tutorial shows how to Extract all partial matches in Excel using the example below; Formula =IF(F5>ct,””,INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5))) Explanation To extract all matches based on a partial match, you can use use an array formula based on the INDEX and AGGREGATE functions, with support from ISNUMBER and SEARCH. In the example shown, the formula in G5 …

Continue Reading

Break ties with helper column and COUNTIF in Excel

by

This tutorial shows how to Break ties with helper column and COUNTIF in Excel using the example below; Formula =A1+(COUNTIF(exp_rng,A1)-1)*adjustment Explanation To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don’t contain duplicates, and therefore won’t result in ties. In the example shown, the formula in D5 …

Continue Reading

Count missing values in Excel

by

This tutorial shows how to calculate Count missing values in Excel using the example below; Formula =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Explanation To count the values in one list that are missing from another list, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in H6 is: =SUMPRODUCT(–(COUNTIF(list1,list2)=0)) Which returns …

Continue Reading

Count cells greater than in Excel

by

This tutorial shows how to Count cells greater than in Excel using the example below; Formula =COUNTIF(range,”>X”) Explanation To count the number of cells that have values greater than a particular number, you can use the COUNTIF function. In the example above  X represents the threshold above which you want to count. In the example …

Continue Reading

Count cells not equal to in Excel

by

This tutorial shows how to Count cells not equal to in Excel using the example below; Formula =COUNTIF(range,”X”) Explanation To count the number of cells that contain values not equal to a particular value, you can use the COUNTIF function. In the example above  X represents the value you don’t want to count. All other values will …

Continue Reading

Count cells equal to one of many things

by

This tutorial shows how to Count cells equal to one of many things using the example below; Formula =SUMPRODUCT(COUNTIF(range,things)) Explanation To count the number of cells equal to one of many values, you can use the COUNTIF function inside of SUMPRODUCT. In the example shown, cell G5 contains this formula: =SUMPRODUCT(COUNTIF(B5:B10,things)) Note COUNTIF is not case-sensitive. …

Continue Reading

Count cells ‘equal to’ in Excel

by

This tutorial shows how to Count cells ‘equal to’ in Excel using the example below; Formula =COUNTIF(range,value) Explanation To count the number of cells equal to a specific values, you can use the COUNTIF function. In the example shown, G6 contains this formula: =COUNTIF(D5:D11,”red”) How this formula works The COUNTIF function is fully automatic — it …

Continue Reading

Count cells that do not contain in Excel

by

This tutorial shows how to Count cells that do not contain in Excel using the example below; Formula =COUNTIF(range,“<>*text*”) Explanation To count the number of cells that do not contain certain text, you can use the COUNTIF function. In the example above “*” is a wildcard matching any number of characters. In the example, the …

Continue Reading

Count cells that contain text in Excel

by

This tutorial shows how to Count cells that contain text in Excel using the example below; Formula =COUNTIF(range,”*”) Explanation To count the number of cells that contain text (i.e. not numbers, not errors, not blank), use the COUNTIF function and a wildcard. “*” is a wildcard matching any number of characters. Do you want to count …

Continue Reading

Count cells equal to either x or y in Excel

by

This tutorial shows how to Count cells equal to either x or y in Excel using the example below; Formula =COUNTIF(range,value1) + COUNTIF(range,value2) Explanation To count the number of cells equal to either one value OR another, you use formula that uses the COUNTIF function twice. In the example shown, cell E5 contains this formula: =COUNTIF(B4:B9,”apples”)+COUNTIF(B4:B9,”pears”) …

Continue Reading

Count cells less than in Excel

by

This tutorial shows how to Count cells less than in Excel using the example below; Formula =COUNTIF(range,”<X”) Explanation To count the number of cells that contain values less than a particular number, you can use the COUNTIF function. In the generic form of the formula (above) rng represents a range of cells that contain numbers, …

Continue Reading

Count cells not equal to many things in Excel

by

This tutorial shows how to Count cells not equal to many things in Excel using the example below; Formula =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) Explanation To count cells not equal to any of many things, you can use a formula based on the MATCH, ISNA, and SUMPRODUCT functions. In the example shown, the formula in cell F5 is: =SUMPRODUCT(–(ISNA(MATCH(data,exclude,0)))) …

Continue Reading

Count cells that contain positive numbers in Excel

by

This tutorial shows how to Count cells that contain positive numbers in Excel using the example below; Formula =COUNTIF(range,”>0″) Explanation To count positive numbers in a range of cells, you can use the COUNTIF function. In the example, the active cell contains this formula: =COUNTIF(B2:B6,”>0″) How this formula works COUNTIF counts the number of cells …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Extract most frequently occurring text in Excel
  • How to extract substring in Excel
  • Replace one character with another in Excel
  • How to split text with delimiter in Excel
  • Two ways to Compare Text in Excel
  • Get project end date in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Extract time from a date and time in Excel
  • Steps to create Dynamic calendar grid in Excel
  • How to calculate working days left in month in Excel
  • CUMPRINC function: Description, Usage, Syntax, Examples and Explanation
  • AMORDEGRC function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
  • ODDLYIELD function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles