Search This Blog

Friday, November 4, 2016

How To Automatically Count The Number Of Times A Number (Numeric Data) or Word (Text Data) Appear In Your Worksheet | Microsoft Excel 2016


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:
  • 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.
Example:

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





SHARE THIS

0 comments:

Search This Blog