Mastering the basic Excel formulas is critical for beginners to become highly proficient in data analysis. A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. Every formula in Excel starts with an equal sign, then the function name and in bracket a …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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-“) …
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 …
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. …
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 …
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. …
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 …
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 …
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 …
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, …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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 …
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. …
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 …
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 …
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 …
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”) …
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, …
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)))) …