To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. In the example shown, the formula in G5 is:
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)...

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 example shown, the formula in C5 is:
=SUM(...

To search multiple worksheets in a workbook for a value and return a count, you can use a formula based on the COUNTIF and INDIRECT functions. With some setup, you use this approach to search an entire workbook for a...

To count unique numeric values in a range, you can use a formula based on the FREQUENCY and SUM functions.
For example, assume you have a list of employee numbers together with hours worked on "Project X", and you want...

To display a check mark if the value passes some kind of test, you can use a formula based on the IF function together a symbolic font like Wingdings.
In the example show, the formula in C5 is:
=IF(COUNTIF(allowed,B5...

To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and "*"...

To count cells that contain either one value or another, you an either use a helper column then tally up the count, or a more complex single cell formula.
Background
When you count cells with "OR" criteria, you need...

To mark the first duplicate in a list, you can use a formula based on the COUNTIF function. Optionally, you can flag subsequent duplicates with a different marker. In the example shown the formula in cell C4 is:
=IF(...

If you need to determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function with wildcards.
In the example shown, the formula in D5...

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(...

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. In the example shown, the formula in C9 is:
=COUNTIF(C5:C8,"<>ok")=0
Note: this formula...

To allow only unique values in a given range, you can use data validation with a custom formula based on the COUNTIF function. In the example shown, the data validation applied to C5:C9 is:
=COUNTIF(emails,C5)<2...

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:...

To dynamically sort text values in alphabetical order, you can use use a formula based on the COUNTIF function. In the example shown, the formula in C5 is:
=COUNTIF(countries,"<="&B5)
where "...

To count the number of cells that have values greater than a particular number, you can use the COUNTIF function. In the generic form of the formula rng represents a range of cells that contain numbers, and X represents...