Excel Formula

Count

Count cells between two numbers

Using COUNTIF instead

The COUNIFS function is built to count cells that meet multiple criteria. In this case, because we supply the same range for two criteria, each cell in the range must meet both criteria in order to be counted.

=COUNTIFS(range,">=X",range,"<=Y")

Using COUNTIF instead

If you have an older version of Excel that doesn't have the COUNTIFS function, you can use the COUNTIF function instead like this:

=COUNTIF(range,">X")-COUNTIF(range,">Y")

Making the criteria variable

If you want to use a value in another cell as part of the criteria, use the ampersand (&) Character to concatenate like this:

=COUNTIF(rng,">"&a1) 

Count cells equal to case sensitive

	Name A Name Count B A 2 A a 1 D b 0 a B 1

  

=SUMPRODUCT((--EXACT(value,range,D3)))

 If you need to count cells that contain certain text in a case-sensitive manner, you can use a formula that uses the EXACT function along with SUMPRODUCT.

In the example, there is a list of names in a named range ("names"), B3:B9. In the range D3:D6, there is another list of names, with duplicates removed. To get the count of "a", you can use the formula:

 =SUMPRODUCT((--EXACT("a",B3:B9)))

In the example, which uses the named range "names", the first formula is:

 =SUMPRODUCT((--EXACT(D3,names))) 

Count cells equal to either x or y

Name
A		Name	Count
B		A + B	4
A		D+ C	1
D
a

=COUNTIF (rng, value1) + COUNTIF (rng, value2)

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,"a") +COUNTIF (B4:B9,"b")