Counting the number of data

To count the number of cells in an Excel data, we use the following function.

=COUNT(range)

[range] = Select the corresponding range in calculating cell number.

Definition of function A function to count the number of cells of a number
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

=COUNTA(range)

[range] = Select the corresponding range in calculating cell number.

Definition of function A function to count the number of cells of a number or character
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

Example of use) Count full cells/COUNT, COUNTA function

The list below is the attendance list for an Excel research group and shows the names of the invited universities and for those who replied, the number of participants. We will count the number of full (not empty) cells in the column ‘Invited University’ and ‘Number (Num.) of participants’, in order to find out ① The number of universities participating and ② The number of universities invited.

① Select cell range of the number of participants, [C5:C9]

We will find the number of full cells in the column of ‘Num. of participants’.
=COUNT(C5:C9)

② Select cell range of the invited universities, [B5:B9]

We will find the number of full cells in the column of ‘Invited university’.
=COUNTA(B5:B9)

POINT!

The function we use will be different according to whether subject to be counted is a ‘number’ or a ‘character (word)’.

COUNT function → Counts full cells with ‘numbers
COUNTA function → Count full cells with ‘numbers or characters

※To count the number of full cells with characters but exclude those with numbers, we use the following formula.

=COUNTA(range A)-COUNT(range A)

Take notice of the following situations leading to miscalculation.

Example of situations that lead to miscalculation)

① The COUNT function counts cells with a ‘0 (zero)’

Even though there are ‘0’ participants the COUNT function counts the number ‘0’, so the calculated number of universities participating becomes ‘4’, though actually it is ‘3’. In this case, either avoid using ‘0’, or count using the COUNTIF function explained further below.

② The COUNTA function counts cells with spaces

Spaces and blanks with nothing in them are also being counted, so we must be careful when making lists and tables.

Counting data with a condition

To count the number of cells according to certain condition, we use the following function.

=COUNTIF(range, criteria)

[range] = Select the corresponding range in calculating the number of cells.
[criteria] = Input the condition using ” (double quotation).

Definition of function A function that appoints a condition in calculating cell number
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013 ・ Excel2016

 

Example of use 1) Count cells that meet a condition (texts, bigger/smaller than)/COUNTIF function

The table below is the daily sales report of an online shop. It shows the ‘Date’ of order, ‘Product’, ‘Price (unit price)’, ‘number’, ‘Total Price’, and if it is ‘Cash on delivery’. We will find ① the number of sales that are ‘Cash of delivery’, and ② The number of times they sold more than 5 of the product.

① Count the number of cells marked with ‘○’ in the ‘Cash on Delivery’ column

(1) [range] : Select cell range from column ‘Cash on Delivery’, [G5:G18].
(2) [criteria] : Input ‘○’ in between the two ” (double quotations).  → “○”
=COUNTIF(G5:G18,”○”)

② Count the number of times they sold more than 5 of the product

(1) [range] : Select cell range from column ‘Number’, [E5:E18].
(2) [criteria] : Use a mathematical symbol to express more than 5. → “>=5”
=COUNTIF(E5:E18,”>=5″)

 

Example of use 2) Count cells that meet a condition (including ~, dates)/COUNTIF function

This time, using the same table, we will extract ①Number of times ‘~ Butter’ has been sold, and ②Number of sales achieved in April 2nd.

① Count the number of cells which has the word ‘Butter’ included in product name.

(1) [range] : Select cell range with product names, [C5:C18].
(2)[criteria] : To look for cells with a certain word (in this case, Butter) like ‘Any word + Butter’, we use the * (asterisk) as follows → “*Butter”
=COUNTIF(C5:C18,”*Butter”)
To count the number of cells which include a certain character or word, we use  * as the arbitrary character. 

Example) To find cells that include the word ‘Butter’

When ‘Butter’ comes in front.
Examples) Butter jam, Butter sandwich, etc.
=COUNTIF(range,”Butter*“)
When ‘Butter’ comes in between.
Examples) American Butter jam, Organic Butter sandwich, etc.
=COUNTIF(range,”*Butter*“)
When ‘Butter’ comes at the back.
Examples) Salted Butter, Organic Butter, etc.
=COUNTIF(range,”*Butter“)

② Counting the number of sales achieved in ‘2-April’.

(1) [range] : Select cell range with dates, [B5:B18].
(2) [criteria] : When selecting a “date” as the criteria, Excel automatically recognizes when dates are written using ‘/ (slash)’ or ‘-‘, like ‘4/2’ or ‘4-2’.  To find the number of sales achieved in 2-April, we input the following → “4/2”
=COUNTIF(B5:B18,”4/2″)

【The judgement of the ‘year’ when selecting a date as the criteria】                                                
If we exclude the ‘year’ in the criteria like ‘4/2’ or ‘4-2, Excel will automatically judge the ‘year’ to be the current (If the current year is 2016, it will search for data with the date ‘2016/04/02’. To search for dates in the past, we also include the year in the criteria, like “2015/4/2” or “2015-4-2”.

Example of use 3) Count cells that meet a condition (space, not empty, besides ~)/COUNTIF function

Using the same table again, we will extract ① the number of sales besides ‘Cash on Delivery’, and ② the number of sales besides Jam.

① Count the number of empty cells in the column ‘Cash on Delivery’.

(1) [range] : Select cell range of the column ‘Cash on Delivery’, [G5:G18].
(2)[criteria] : To find empty cells, we do not input anything in between “” (double quotations), as follows → “”
=COUNTIF(G5:G18,””)

【Counting cells that are not empty】
To count cells that are not empty, we use the symbol <> which indicates ‘besides/other than ~’, and the symbol “” which indicates empty. We combine the two symbols to create the following formula.
Example 1) =COUNTIF( G5:G18 , “<>” & “” )
Example 2) =COUNTIF( G5:G18 , “<>” )

② Count the number of sales besides Jam.

(1) [range] : Select cell range of the column ‘Products’, [C5:C18].
(2) [criteria] : If we want the condition to be ‘besides ~’, we use the symbol <>. In order to search for the number of products ‘besides Jam’, we input the following. → “<>Jam”
=COUNTIF(C5:C18,”<>Jam”)

Example of use 4) Count cells that meet a condition (duplication check)/COUNTIF function

If is not uncommon to make a mistake of inputting the same data twice. This time, we will use the ‘COUNTIF function’ for the following list of employees to check that there is no duplication in names. We will ① count how many names there are. If no duplication, the number ‘1’, if yes, a number bigger than ‘1’ will appear in the column ‘Duplication’ check.

① Count the number of names for a duplication check.

(1) [range] : Select cell range with employee names, [B5:B12].
(2) [criteria] : Select each cell of employee name. In order to select a specific cell to search for, we must specify its position. → 5th row : 「B5」、6th row : 「B6」、7th row : 「B7」…(※Change the subject (position of cell) for every row)
5th row) =COUNTIF(B5:B12,B5) 、6th row) =COUNTIF(B5:B12,B6)

 

POINT!

Below are the symbols that help us limit the condition when searching.
The Wildcard characters are useful when placing a condition with characters (text).

【Symbols (equal to, unequal to, more than, less than)】

Symbol Definition
< Smaller than the selected number
<= Equal to or smaller than the selected number
> Bigger than the selected number
>= Equal to or bigger than the selected number
<> Not equal to selected number (besides/other than)

【Wildcard characters (Searching for words which include the specified character)】

Symbol Definition
* Allows more than one character
? Allows one character

With the symbols above and “” (double quotations) that helps us search for empty cells, we can search and count data in many ways.

Counting with multiple conditions

To set up multiple conditions when counting the number of cells, we use the following function.

=COUNTIFS(range1, criteria1, [range2, criteria2]…)

[range1,2,…] = Select cell range for counting the number of cells.
[criteria1,2,…] = Use ” (double quotation) to set up a condition

Definition of function Counting the number of cells that meet 2 or more specified conditions
EXCEL version Excel2007 ・ Excel2010 ・ Excel2013 ・ Excel2016

 

Example of use) Count cells that meet multiple conditions/COUNTIFS function

Here, we will use the sales report below to count the number of cells that meet more than one condition. The COUNTIFS function is useful for this kind of calculation. For example, we will count the number of cells that meet the following conditions : ‘① (All) Butter products, and ② The number sold is less than 4.

 

① Select 1st condition, ‘(All) Butter products’.

(1) [range1] : Select cell range with product names, [C5:18].
(2)[criteria1] : To select words that include ‘Butter’ like ‘~ Butter’, use the * (asterisk) as follows → “*Butter”
⇒To set up a condition of search that ‘includes’ a character, click here for reference

② Select 2nd condition, ‘The number sold is less than 4’.

(1) [range2] : Select cell range of the ‘Number’ column, [E5:E18].
(2)[criteria2] : Use symbol to express less than 4. → “<=4”
⇒For information over symbols, click here for reference

=COUNTIFS(C5:C18,”*Butter”,E5:E18,”<=4″)

 

POINT!

The COUNTIFS funcion allows us to set up mutiple conditions, but it will only search for ‘data that meets ALL of the conditions‘. Nevertheless, this function allows us to research the data through a more detailed perspective.