Have you ever find yourself in a situation where you need to know how many cell contains a certain data? Perhaps, you want do know how many times someone make an order, or you just want to know how many cell contain a particular number or word.
If yes, then using the Find feature might not be your best solution. The best option is to use the COUNTIF function.
This function is able to count the number of cell that contains a number or word of your choice in a given rage.
The syntax of the countif function in Excel 2016 is:
=countif(range, criteria)
the RANGE represent the range of the cell you want it to look for the cell containing your query from, while CRITERIA represent your query, which can be, either number or text/word.
Note:
Copy the content of the table below and paste it into a worksheet in Excel.
If yes, then using the Find feature might not be your best solution. The best option is to use the COUNTIF function.
This function is able to count the number of cell that contains a number or word of your choice in a given rage.
The syntax of the countif function in Excel 2016 is:
=countif(range, criteria)
the RANGE represent the range of the cell you want it to look for the cell containing your query from, while CRITERIA represent your query, which can be, either number or text/word.
Note:
- Your query (criteria) should not be more than 255 characters long, else the countif function will return an incorrect result.
- Countif function is not case sensitive.
- If you are using text or word as your criteria, then the text needs to be in quote, but if it is number, it is not compulsory.
Copy the content of the table below and paste it into a worksheet in Excel.
Order List
|
|||
S/N
|
Names of
Customers
|
Items Ordered
|
Quantity
|
1
|
John
|
Apples
|
15
|
2
|
Stella
|
Watermelon
|
7
|
3
|
Emmanuel
|
Orange
|
20
|
4
|
Fredrick
|
Apples
|
11
|
5
|
Onyeya
|
Peaches
|
15
|
6
|
Gad
|
Apples
|
30
|
7
|
Wilsom
|
Grape
|
7
|
8
|
Kate
|
Watermelon
|
20
|
9
|
Mba
|
Peaches
|
15
|
10
|
Odor
|
Orange
|
30
|
11
|
Monday
|
Apples
|
7
|
12
|
Debor
|
Orange
|
11
|
13
|
Samuel
|
Watermelon
|
15
|
To get the number of customers that of customers that order Apples use =COUNTIF(C3:C15, "Apples") or =COUNTIF(C3:C13, C3)
If it is the number of customers that order a quantity of 15 then use =COUNTIF(D3:D15, "15") or =COUNTIF(D3:D15, 15) or =COUNTIF(D3:D15, D3).
As mentioned above, you do not need to quote the criteria if it is a number but if it is a text you need to do that.
Countif is an Excel function, so it can/might work in any version of Microsoft Excel program and not only version 2016.
Team Pinfoltd
0 comments: