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

To count the number of cells that begin with 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 begin with, and...

To count the number of cells that contain values between two numbers in a range of cells, you can use the COUNTIFS function. In the generic form of the formula (above) range represents a range of cells that contain...

If you want to test a list for duplicates, you can do so with a formula that uses COUNTIF together with SUMPRODUCT.
In the example, there is a list of names in the range B3:B11. If you want to test this list to see if...

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 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 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 on INDEX and MATCH...

To count the number of cells that contain a certain number of characters text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, and "?" is a wildcard matching any...

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, the active cell contains this formula:
=COUNTIF(B4:B9,"apples")+...

To count the number of cells equal to one of many values, you can use the COUNTIF function inside of SUMPRODUCT. In the generic form of the formula (above) rng represents a range of cells, and things represents the...

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

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 list in...

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

If you need to count unique numeric values in a range, you can use a formula that uses the FREQUENCY function together with the SUM function.
For example, assume you have a list of employee numbers together with hours...