Practice question 1/Count cells that meet a condition/Empty, full cells

【Practice question 1 : Question】

Below is a business trip schedule of the Sales manager.
It shows the location of the trip and if the tickets for transportation are arranged.
1. Calculate the number of trips in which the tickets are arranged.
2. Calculate the number of trips in which the tickets are not arranged yet.

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function to count data・・・Excel count condition function
Find the way to count empty spaces using the function・・・Excel count condition space function
Find the way to count full cells using the function・・・Excel count condition full cells function
Find the way to specify a condition to the function・・・Excel count condition specify function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 1 : Answer】

Below are the answers to practice question 1.

※The answer for number of trips in which tickets are arranged is in blue, and the number of trips in which tickets are not arranged yet is in red.

【Cell】【Formula】【Value】    
D12 :=COUNTIF(D4:D10,”<>”) ⇒    2      

※As for the above, the formulas listed below are also correct.
In the answer above we are focusing on ‘counting cells that are not empty’.
   【Alternate answer 1】D12 =COUNTIF( D4:D10 ,”” & “” )
   【Alternate answer 2】D12 =COUNTIF( D4:D10,”Arranged” )

【Cell】【Formula】【Value】    
D14 :=COUNTIF(D4:D10,””)      ⇒    5      

▶Download answers in Excel

【Practice question 1 : Explanation】

Below is the explanation to practice question 1.

To count full cells, we use the COUNTIF function.

=COUNTIF(range, criteria)
[range] = Select the corresponding range in calculating the number of cells.
[criteria] = Input the condition using ” (double quotations).
【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( D4:D10 , “<>” & “” )
Example 2) =COUNTIF( D4:D10 , “<>” )

If at least one cell meets a condition, the COUNTIF function will calculate the number of data (cells) that meet the certain condition.

Always add ” (double quotations) to the condition. The example diagram below shows the answer to cell D12.

The example diagram below shows the answer to cell D14.

【Practice question 1 : Wrap-up】

Remember that the COUNTIF function counts the number of cells that meet a specified condition.
There are a few ways to specify a condition, so begin memorizing the simple ways and move up to the more complicated ways.

=COUNTIF(range, criteria)
[range] = Select the corresponding range in calculating the number of cells.
[criteria] = Input the condition using ” (double quotations).
The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the COUNTIF function/ Count the number of cell that meets a condition (empty・not empty).

 

Practice question 2/Count cells that meet a condition/Other than ~

【Practice question 2 : Question】

Below is a business trip schedule.
Other than the location of the trip and the situation of ticket arrangement, there is a column showing the amount of trip allowance for each trip.
1. Count the number of trips to a ‘branch’.
2. Count the number of trips to a location other than a ‘branch’.
3. Count the number of trips with the amount of trip allowance other than $80.

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function to count data・・・Excel count condition function
Find the way to set a condition to the function・・・Excel count condition specify function
Find the way to set a textual condition to the function・・・Excel count condition text specify function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 2 : Answer】

Below are the answers to practice question 2.

※The answers for the number of trips to a branch is in blue, the number of trips to a location other than a branch is in red, and the number of trips with trip allowance other than $80 is in green.

【Cell】【Formula】【Value】    
D12 :=COUNTIF(C4:C10,”*branch”) ⇒    4      

【Cell】【Formula】【Value】    
D14 :=COUNTIF(C4:C10,”<>*branch”)      ⇒    3      

【Cell】【Formula】【Value】    
D16 :=COUNTIF(E4:E10,”<>80″)      ⇒    5      

▶Download answers in Excel

【Practice question 2 : Explanation】

Below are the explanations to practice question 2.

We can also use the COUNTIF function to count cells with a specific text in them.

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

(a) Count the number of trips to a ‘branch’.

(1) [range] : Highlight cell range with the ‘locations’, cell range [C4:C10].
(2) [criteria] : To count the number of cells which include a certain text like ‘a word + branch’ , we use * as the arbitrary character. → “*branch”
=COUNTIF(C4:C10,”*branch”)

(b) Count the number of trips to a location other than a ‘branch’.

(1) [range] : Highlight cell range with the ‘locations’, cell range [C4:C10].
(2) [criteria] : To include a condition defining ‘besides/other than ~’, we use the symbol <>. → “<>*branch”
=COUNTIF(C4:C10,”<>*branch”)

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 ‘pen’

When ‘pen’ comes in front
Examples) pen stand, pen holder, etc.
=COUNTIF(range,”pen*“)
When ‘pen’ comes in between.
Examples) color pen set, brush pen lettering
=COUNTIF(range,”*pen*“)
When ‘pen’ comes in the end.
Examples) ballpoint pen, fountain pen, etc.
=COUNTIF(range,”*pen“)

(c) Count the number of trips with trip allowance other than $80.

(1) [range] : Highlight cell range with the ‘trip allowances ($)’, cell range [E4:E10].
(2) [criteria] : To include a condition defining ‘besides/other than ~’, we use the symbol <>. → “<>80”
=COUNTIF(E4:E10,”<>80”)

We count the number of cells using the COUNTIF function.

There are 4 trips to a ‘branch’,
3 trips to a location other than a ‘branch’, and
5 trips with a trip allowance other than $’80.00′.

【Practice question 2 : Wrap-up】

Remember that the COUNTIF function counts the number of cells that meet a specified condition.
There are a few ways to specify a condition, so begin memorizing the simple ways and move up to the more complicated ways.

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

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the COUNTIF function/ Count the number of cell that meets a condition (spaces・not empty).

 

Practice question 3/Count cells that meet a condition/Duplication

【Practice question 3 : Question】

Below is a product master data.
The shop is considering a change in price for some products and is renewing this master data.
Please check if there is no duplication in this data.

▶Download data in Excel

Hints!

Even though we do not know what function to use, it will not be a problem if we can find the correct function by searching the internet. Therefore, learning how to type the relevant keywords in the search engine is important. Other than using the search box in this website, search engines such as Google and Yahoo! can be helpful. In this case, keywords like below will lead us to get what we wish for, so when having trouble, use the internet.

【Keywords for search】
Find the function to count data・・・Excel count condition function
Find the way to specify a condition to the function・・・Excel count condition specify function
Find the way to set a condition to check duplication・・・Excel count condition duplication function
Search box for this website
※There are more keywords other than the above.

 

【Practice question 3 : Answer】

Below are the answers to practice question 3.

※The answers are in red.
※We will explain “$” that symbolizes absolute cell reference in the explanation.

【Cell】【Formula】【Value】  
D4 :=COUNTIF($B$4:$B$18,B4) ⇒  1    
D5 :=COUNTIF($B$4:$B$18,B5) ⇒  1    

          :
        【Same with D6~D16…】
          :

 

D17 :=COUNTIF($B$4:$B$18,B17) ⇒  1    
D18 :=COUNTIF($B$4:$B$18,B18) ⇒  2    

▶Download answers in Excel

 

【Practice question 3 : Explanation】

Checking duplication in a data

Duplication check helps us to check when there are 2 or more of the exact same word or text in a data.

To search for duplication in data,
we use the COUNTIF function.

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

The correct formula for this practice question is shown below.

(1) [range] : Highlight cell range with product names, cell range [$B$4:$B$18].

For this case we use the ‘absolute cell reference’ that fixes a specific cell within the selected [range].
We copy the content written in cell D4 by dragging it down (keep on clicking left on mouse when dragging down) to cell D5~D18.
We use the symbol ‘$’ to define an absolute cell reference.
【Reference】For more information on absolute cell reference, click here

Why do we need the symbol ‘$’?
If we write the formula in cell D4 like this ” =COUNTIF(B4:B18,B4) ” without absolute cell reference,
once we drag down the formula until cell D
the selected cell range (B4:B18) will shift a row each. We want to keep the selected cell range fixed.

(2) [criteria] : We want to check for duplication, so here we must select a cell that has the word/text we wish to check.
If the results show the value of 2 or more, then it means that there are more than one of that specific content.
To check for duplication, we write the function as below:
In the case of cell D4 → =COUNTIF($B$4:$B$18,B4)
We will now count how many duplicates there are in the data.
Pink : There is only 1 ‘Calculator (12 digits)’ within the cell range surrounded in red borders. No duplication.
Blue : There are 2 ‘Mechanical pencil’. Duplication.
Green : There are 2 ‘Colored pencils 24-count’. Duplication.

【Practice question 3 : Wrap-up】

Remember that the COUNTIF function counts the number of cells that meet a specified condition.
There are a few ways to specify a condition, so begin memorizing the simple ways and move up to the more complicated ways. We can use the COUNTIF function to search for empty and full cells, a certain text, or duplication.

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

The function that we used for this practice question is explained in detail in the following link.
Feel free to check and revise with our website.

データをカウントする
Describing the COUNTIF function/ Check for duplication.