Counting Formulas

Count cells equal to 2 values	Counting - Equal To
Count cells equal to 3 or more values	Counting - Equal To
Count cells equal to one of many	Counting - Equal To
Count cells equal to this or that	Counting - Equal To
Count cells in a table when a column is greater than a value	Counting - Table
Count cells in a table when a column is less than a value	Counting - Table
Count cells in a table when a column is unique with 1 condition	Counting - Are
Count cells in a table when a column meets 1 condition	Counting - Table
Count cells in a table when a column meets multiple conditions	Counting - Table
Count cells not equal to 2 values	Counting - Not Equal To
Count cells not equal to 3 or more values	Counting - Not Equal To
Count cells not equal to a value	Counting - Not Equal To
Count cells not equal to one of many	Counting - Not Equal To
Count cells that are blank	Counting - Equal To
Count cells that are dates	Counting - Equal To
Count cells that are dates and either x or y	Counting - Contain
Count cells that are dates and equal to a value	Counting - Contain
Count cells that are dates and in a particular month	Counting - Contain
Count cells that are dates and in a particular year	Counting - Contain
Count cells that are dates and not between 2 dates	Counting - Contain
Count cells that are dates and not equal to a value	Counting - Contain
Count cells that are dates and unique	Counting - Contain
Count cells that are dates between 2 dates	Counting - Contain
Count cells that are dates by day of week	Counting - Other
Count cells that are errors	Counting - Contain
Count cells that are hidden	Counting - Other
Count cells that are not blank	Counting - Not Equal To
Count cells that are not dates	Counting - Not Equal To
Count cells that are not errors	Counting - Not Equal To
Count cells that are not hidden	Counting - Not Equal To
Count cells that are not numbers	Counting - Contain
Count cells that are not text	Counting - Not Equal To
Count cells that are not text or numbers	Counting - Contain
Count cells that are numbers	Counting - Contain
Count cells that are numbers and do not equal a value	Counting - Contain
Count cells that are numbers and do not equal either x or y	Counting - Contain
Count cells that are numbers and either x or y	Counting - Contain
Count cells that are numbers and equal to a value	Counting - Contain
Count cells that are numbers and even	Counting - Contain
Count cells that are numbers and greater than a value	Counting - Contain
Count cells that are numbers and less than a value	Counting - Contain
Count cells that are numbers and negative	Counting - Contain
Count cells that are numbers and odd	Counting - Are
Count cells that are numbers and positive	Counting - Contain
Count cells that are numbers and unique	Counting - Are
Count cells that are numbers in a range	Counting - Other
Count cells that are numbers with leading zeros	Counting - Other
Count cells that are text	Counting - Are
Count cells that are text and begin with	Counting - Contain
Count cells that are text and contain either x or y	Counting - Contain
Count cells that are text and contain a value	Counting - Other
Count cells that are text and contain a value case sensitive	Counting - Other
Count cells that are text and do not begin with	Counting - Contain
Count cells that are text and do not contain a value	Counting - Other
Count cells that are text and do not equal a value	Counting - Other
Count cells that are text and do not equal either x or y	Counting - Other
Count cells that are text and do not end with	Counting - Contain
Count cells that are text and either x or y	Counting - Other
Count cells that are text and end with	Counting - Other
Count cells that are text and equal to a value	Counting - Contain
Count cells that are text and equal to a value case sensitive	Counting - Contain
Count cells that are text and n characters long	Counting - Contain
Count cells that are text and over n characters long	Counting - Other
Count cells that are text and unique	Counting - Are
Count cells that are visible	Counting - Other
Count cells with formatting	Counting - Conditional
Count cells with shading	Counting - Conditional
Count differences between 2 cell ranges	Counting - Conditional
Count occurrences in entire workbook	Counting - Other
Count or sum variance	Counting - Other
Count substring occurrences	Counting - Other
Count times in a particular range	Counting - Other
Count total characters in range	Counting - Other
Count total matches in 2 ranges	Counting - Other
Count unique dates	Counting - Other
Count unique numbers	Counting - Other
Count unique numbers with 1 condition	Counting - Other
Count unique text strings	Counting - Other
Count unique text strings with 1 condition	Counting - Other
Count unique values	Counting - Other
Count visible cells	Counting - Other
Count visible columns	Counting - Other
Count visible rows	Counting - Other
Count visible rows in a filtered column	Counting - Other
Count visible rows with 1 condition	Counting - Other
Count visible rows with multiple conditions	Counting - Other
Count whole numbers only	Counting - Other

Count cells equal to 2 values Counting - Equal To
Count cells equal to 3 or more values Counting - Equal To
Count cells equal to one of many Counting - Equal To
Count cells equal to this or that Counting - Equal To
Count cells in a table when a column is greater than a value Counting - Table
Count cells in a table when a column is less than a value Counting - Table
Count cells in a table when a column is unique with 1 condition Counting - Are
Count cells in a table when a column meets 1 condition Counting - Table
Count cells in a table when a column meets multiple conditions Counting - Table
Count cells not equal to 2 values Counting - Not Equal To
Count cells not equal to 3 or more values Counting - Not Equal To
Count cells not equal to a value Counting - Not Equal To
Count cells not equal to one of many Counting - Not Equal To
Count cells that are blank Counting - Equal To
Count cells that are dates Counting - Equal To
Count cells that are dates and either x or y Counting - Contain
Count cells that are dates and equal to a value Counting - Contain
Count cells that are dates and in a particular month Counting - Contain
Count cells that are dates and in a particular year Counting - Contain
Count cells that are dates and not between 2 dates Counting - Contain
Count cells that are dates and not equal to a value Counting - Contain
Count cells that are dates and unique Counting - Contain
Count cells that are dates between 2 dates Counting - Contain
Count cells that are dates by day of week Counting - Other
Count cells that are errors Counting - Contain
Count cells that are hidden Counting - Other
Count cells that are not blank Counting - Not Equal To
Count cells that are not dates Counting - Not Equal To
Count cells that are not errors Counting - Not Equal To
Count cells that are not hidden Counting - Not Equal To
Count cells that are not numbers Counting - Contain
Count cells that are not text Counting - Not Equal To
Count cells that are not text or numbers Counting - Contain
Count cells that are numbers Counting - Contain
Count cells that are numbers and do not equal a value Counting - Contain
Count cells that are numbers and do not equal either x or y Counting - Contain
Count cells that are numbers and either x or y Counting - Contain
Count cells that are numbers and equal to a value Counting - Contain
Count cells that are numbers and even Counting - Contain
Count cells that are numbers and greater than a value Counting - Contain
Count cells that are numbers and less than a value Counting - Contain
Count cells that are numbers and negative Counting - Contain
Count cells that are numbers and odd Counting - Are
Count cells that are numbers and positive Counting - Contain
Count cells that are numbers and unique Counting - Are
Count cells that are numbers in a range Counting - Other
Count cells that are numbers with leading zeros Counting - Other
Count cells that are text Counting - Are
Count cells that are text and begin with Counting - Contain
Count cells that are text and contain either x or y Counting - Contain
Count cells that are text and contain a value Counting - Other
Count cells that are text and contain a value case sensitive Counting - Other
Count cells that are text and do not begin with Counting - Contain
Count cells that are text and do not contain a value Counting - Other
Count cells that are text and do not equal a value Counting - Other
Count cells that are text and do not equal either x or y Counting - Other
Count cells that are text and do not end with Counting - Contain
Count cells that are text and either x or y Counting - Other
Count cells that are text and end with Counting - Other
Count cells that are text and equal to a value Counting - Contain
Count cells that are text and equal to a value case sensitive Counting - Contain
Count cells that are text and n characters long Counting - Contain
Count cells that are text and over n characters long Counting - Other
Count cells that are text and unique Counting - Are
Count cells that are visible Counting - Other
Count cells with formatting Counting - Conditional
Count cells with shading Counting - Conditional
Count differences between 2 cell ranges Counting - Conditional
Count occurrences in entire workbook Counting - Other
Count or sum variance Counting - Other
Count substring occurrences Counting - Other
Count times in a particular range Counting - Other
Count total characters in range Counting - Other
Count total matches in 2 ranges Counting - Other
Count unique dates Counting - Other
Count unique numbers Counting - Other
Count unique numbers with 1 condition Counting - Other
Count unique text strings Counting - Other
Count unique text strings with 1 condition Counting - Other
Count unique values Counting - Other
Count visible cells Counting - Other
Count visible columns Counting - Other
Count visible rows Counting - Other
Count visible rows in a filtered column Counting - Other
Count visible rows with 1 condition Counting - Other
Count visible rows with multiple conditions Counting - Other
Count whole numbers only Counting - Other

Count cells equal to 2 values

	A
1	COUNTIF(CellRange,value1) + COUNTIF(CellRange,value2)

Count cells equal to 3 or more values

	A
1	=SUMPRODUCT(COUNTIF(CellRange,{value1, value2, value3}))

Count cells equal to one of many

	A
1	??

Count cells in a table when a column meets 1 condition

	A
1	{=SUM((CellRange>5) * (CellRange<15) * CellRange)}
2	Returns #N/A if any of the cells are not numeric
3	{=SUM((IF(ISERROR(CellRange),0,CellRange)>5)(IF(ISERROR(CellRange),0,CellRange)<15)IF(ISERROR(CellRange),0,CellRange))} = 156

link - Count cells equal to 3 or more values

Count cells not equal to 3 or more values

	A
1	=COUNTA(CellRange) - SUMPRODUCT(COUNTIF(CellRange, {value1, value2, value3}))

Count cells that are blank

	A
1	=COUNTBLANK(CellRange)

Count cells that are dates and in a particular year

You can display the number of cells that contain a date in a particular year by using the COUNTIFS function.

	A	B	C	D
1	=DATE(B1, 1, 1) = 01 Jan 2020	2020
2	=DATE(B1, 12, 31) = 31 Dec 2020
3	=DATE(B3, 1, 1) = 01 Jan 2021	2021
4	=DATE(B3, 12, 31) = 31 Dec 2021
5	=DATE(B5, 1, 1) = 01 Jan 2022	2022
6	=DATE(B5, 12, 31) = 31 Dec 2022
7
8	=COUNTIFS(D8:D15, ">="&B8, D8:D15, "<="&B9) = 2	01 Jan 2020	2020	01 Jul 2020
9	=COUNTIFS(D8:D15, ">="&B10, D8:D15, "<="&B11) = 4	31 Dec 2020		24 Oct 2021
10	=COUNTIFS(D8:D15, ">="&B12, D8:D15, "<="&B13) = 2	01 Jan 2021	2021	03 Mar 2021
11		31 Dec 2021		02 Dec 2022
12		01 Jan 2022	2022	11 Dec 2020
13		31 Dec 2022		24 Feb 2021
14				25 Feb 2022
15				16 Apr 2021

This function uses two criteria to satisfy two conditions.
Lets assume the years have been entered into a column.
The first step is to create a date which is the first day of that year.
The second step is to create a date which is the last day of that year.
Once we have the start and end dates we can use these dates to create our criteria.
The first criteria is any date greater than or equal to the start date.
The second criteria is any date less than or equal to the end date.
The COUNTIFS function was added in Excel 2007.
Before Excel 2007 there were two ways to perform conditional counting.
One was using the SUMPRODUCT function and the other was using Array Formulas.
If you are using the SUMPRODUCT function you will also need to use the double minus operator to convert boolean values into there corresponding ones and zeros.
COUNTIFS
DATE

Count cells that are dates between 2 dates

You can count how many cells contain values in a particular range using the COUNTIFS function.

	A	B	C
1	=DATEVALUE("02 May 22") = 02 May 2022	01 May 2022
2	=DATE(2022, 5, 5) = 05 May 2022	02 May 2022
3		03 May 2022
4		04 May 2022
5		05 May 2022
6		06 May 2022
7
8
9	=COUNTIFS(C9:C14, ">="&B9, C9:C14, "<="&B10) = 4	02 May 2022	01 May 2022
10		05 May 2022	02 May 2022
11			03 May 2022
12			04 May 2022
13			05 May 2022
14			06 May 2022

This formula can return the number of cells between two dates.
How many dates are there between 2 May and 5 May (inclusive).
Lets assume the dates have been entered into a column.
The first step is to create a date that is the first date in the range.
The second step is to create a date that is the last last in the range.
If you have a string date you can pass this to the DATEVALUE function.
If you have year/month/day components you can pass these to the DATE function.
Once we have the start and end date we can use these dates to create our criteria.
The first criteria is any date greater than or equal to the start date.
The second criteria is any date less than or equal to the end date.
COUNTIFS
DATE
DATEVALUE
COUNTBETWEEN - Returns the number of cells that have a value that is between a range.

Count cells that are errors

	A
1	{=COUNT(IF(ISERROR(CellRange)*1))} = 2
2	{=COUNT(IF(ISERROR(CellRange),1))} = 2
3	{=COUNT(IF(ISERROR(CellRange),1,""))} = 2
4	=COUNTIF(cellrange,"#DIV/0!")

COUNTIF
To count the number of cells that contains a specific error
Refer to Conditional Counting

Count cells that are not blank

	A
1	=COUNTA(CellRange)

Count cells that are not hidden

The functions COUNT, COUNTA and COUNTIFS all include hidden cells.

	A
1	aggregate(2,5,B2:D10 = 12
2	subtotal(102,B2:D10) = 12

It is possible to only count cells in a range that are currently visible.
SS - B2:D10 populated
SS - count = 24, counta = 24, countifs(>0) = 24
Column "C" has been manually hidden
Rows "5" and "6" have been manually hidden
The status bar at the bottom can also provide a count but be careful.
When you have hidden rows the status bar will exclude them.
But when you have hidden columns the status bar does not exclude them.
To make sure the status bar is always correct you need to only select the visible cells in a range.
Select the whole range ("B2:D10") and press (Home > Find & Select > Go) to display the GoTo dialog box.
Press Special and select "visible cells only". Press OK.
COUNT
COUNTA
COUNTIFS
AGGREGATE
SUBTOTAL
COUNTVISIBLE - Returns the number of visible, non blank cells in a range.
COUNTIFSVISIBLE - Returns the number of visible, non blank cells that satisfies multiple conditions.

Count cells that are not text

	A
1	={SUM(IF(ISNONTEXT(),1))}

Count cells that are numbers

	A
1	{=SUM(IF(ISNUMBER(CellRange),1,0))} = 46

Count cells that are numbers and do not equal either x or y

	A
1	=SUMPRODUCT(--((ISNUMBER(FIND("chars1",CellRange)) + ISNUMBER(FIND("chars2",CellRange)) ) > 0)

Count cells that are numbers and greater than a value

	A
1	=COUNTIFS("B1:B7,">20")
2	{=B2:G2>5} = {TRUE , TRUE , FALSE , FALSE , TRUE , TRUE}

Count cells that are numbers and negative

	A
1	=COUNTIF(CellRange,"<0")

Count cells that are numbers and odd

	A
1	=SUMPRODUCT(--MOD(CellRange,2)=1))

Count cells that are numbers and positive

	A
1	=COUNTIF(CellRange,">0")

Count cells that are numbers and unique

	A
1	{=SUM(IF(FREQUENCY(CellRange,CellRange)>0,1,0))} = #N/A

This formula does not work when some of the cells contain error values.

Count cells that are numbers in a range

You can display the number of cells that are in a particular range by using the COUNTIFS function.

	A	B	C	D
1	=TEXTBEFORE(B1, "-") = 0	0-20
2	=TEXTAFTER(B1, "-") = 20
3	=TEXTBEFORE(B3, "-") = 21	21-30
4	=TEXTAFTER(B3, "-") = 30
5	=TEXTBEFORE(B5, "-") = 31	31-50
6	=TEXTAFTER(B5, "-") = 50
7	=TEXTBEFORE(B7, "-") = 51	51-70
8	=TEXTAFTER(B7, "-") = 70
9
10	=COUNTIFS(D10:D17, ">="&B10, D10:D17, "<="&B11) = 1	0	0-20	29
11	=COUNTIFS(D10:D17, ">="&B12, D10:D17, "<="&B13) = 1	20		77
12	=COUNTIFS(D10:D17, ">="&B14, D10:D17, "<="&B15) = 2	21	21-30	64
13	=COUNTIFS(D10:D17, ">="&B16, D10:D17, "<="&B17) = 3	30		19
14		31	31-50	35
15		50		63
16		51	51-70	55
17		70		32
18	=LEFT(B18, FIND("-", B18)- 1) = 0	0-20
19	=RIGHT(B18, LEN(B18)-FIND("-", B18)) = 20
20	=LEFT(B20, FIND("-", B20)- 1) = 21	21-30
21	=RIGHT(B20, LEN(B20)-FIND("-", B20)) = 30
22	=LEFT(B22, FIND("-", B22)- 1) = 31	31-50
23	=RIGHT(B22, LEN(B22)-FIND("-", B22)) = 50
24	=LEFT(B24, FIND("-", B24)- 1) = 51	51-70
25	=RIGHT(B24, LEN(B24)-FIND("-", B24)) = 70

This function uses two criteria to satisfy two conditions.
The number must be greater than or equal to the smallest number.
The number must be less than or equal to the largest number.
Lets assume the ranges have been entered into a column.
The range has been entered with a dash in between the smallest number and the largest number.
The first step is to extract the smallest and largest numbers from these values.
The smallest number can be obtained using the TEXTBEFORE function.
The largest number can be obtained using the TEXTAFTER function.
Once we have the smallest and largest values we can use these numbers to create our criteria.
The first criteria is any value greater than or equal to the smallest number.
The second criteria is any value less than or equal to the largest number.
**Alternative Formula - LEFT, RIGHT, FIND, LEN
Before Excel 365 you can use these formulas to get the smallest and largest numbers.
The smallest number could be obtained using the LEFT and FIND functions.
The largest number could be obtained using the RIGHT and LEN functions.
**Alternative Formula - SUM, IF
You can use the SUM function and IF function.
Array Formulas.
**Alternative Formula - SUMPRODUCT
You can use the SUMPRODUCT function
COUNTIFS
TEXTBEFORE
TEXTAFTER
COUNTBETWEEN - Returns the number of cells that have a value that is between a range.

Count cells that are text

	A
1	=COUNTIF(CellRange,"chars")
2	={SUM(IF(ISTEXT(),1))}

Count cells that are text and begin with

	A
1	=COUNTIF(CellRange,"start*")

Count cells that are text and contain either x or y

You can display the number of cells that contain either value X or value Y using the COUNTIFS function.

	A
1	=COUNTIFS(B1:C5,"mon") + COUNTIFS(B1:C5,"tue")
2	=SUM(COUNTIF(B1:C5. {"mon", "tue}))
3	=SUM(IF((B1:C5="mon") + (B1:C5="tue"), 1, 0))
4	=SUMPRODUCT((B1:C5="mon") + (B1:C5="tue))

This uses an array of possible values
Alternative Formula - SUM, IF
You could combine the SUM function and the IF function
Alternative Formula - SUMPRODUCT
This formula is quite slow for large data

Count cells that are text and either x or y

	A
1	=COUNTIF(CellRange,"start*")

Count cells that are text and end with

	A
1	=COUNTIF(CellRange,"start*")

Count cells that are text and equal to a value case sensitive

	A
1	=SUMPRODUCT((--EXACT(value, CellRange)))

Count cells that are text and n characters long

	A
1	{=SUM(IF((CellRange<5) + (CellRange>15), CellRange))}

Returns #N/A if any of the cells are not numeric

Count cells that are text and unique

	A
1	{=SUM( 1/COUNTIF(cellrange, cellrange))}

Count differences between 2 cell ranges

	A
1	{=SUM(1*(CellRange1 <> CellRange2))}
2	{=SUM(IF(CellRange1 = CellRange2, 0, 1))}

SUM
Comparing Two Tables

Count total characters in range

	A
1	={SUM(LEN(A1:A10))}