COUNTIF and COUNTA FUNCTION
COUNTIF FUNCTION
COUNTIF function is used to counts the number of times a specified value as a criteria has been repeated within the specified range of cells . For example you have english marks of your class of 40 students and you want to know the number of students who have secured marks equal to 90 , then countif function will help you to find exact number of students who have secured 90 in easy and quick manner.
Formula has to given as :
= COUNTIF (range,criteria)
Where,
range - the range of cells that you want to count the data. There has to be an ongoing area.
Criteria - a condition that must be met for cell counting.
Criteria can be any of the following :
= Equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
<> not equal to
All these operators must be enclosed in quotation marks and the ampersand (&) is needed in to add as as to combine values and operator . By default the formula will use the equal to “=” operator with value in criteria.
Example 1:
If you want to find the number of students who secured 90 marks in english from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Go to Insert menu and select Function from drop down Menu , a Function Wizard box will appear. Select the ‘MATHEMATICAL’ category under the category and search for function ‘COUNTIF’ in function list.
3. Type the name of function - COUNTIF after the “=” sign in formula text box and type open parenthesis(bracket) after the name of function . Fields range and criteria appears as highlighted below. Now select the range of cells or type it inside the parenthesis of which the instances need to be counted (Eg: corresponding to english marks ) which has a numerical value. The selected range appears in the formula.
1. Give your condition in criteria section (in this example, as you want to find students scored equal to 90, no need to use any operator since by default it will take the “=” operator) and close the bracket in the formula section.
2. Click OK and the counted numbers gets displayed on the selected cell.
Example 2:
If you want to find the number of students who secured above 90 marks in english from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Go to Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘MATHEMATICAL’ category under the category and search for function ‘COUNTIF’ in function list.
3. As in the previous example, type the function COUNTIF and select the range of values to checked (Example: English marks column)
4. Give your condition in criteria section and mention the operator (Use “>=” as you need to find greater than or equal to 90) and use “&” to combine both value (Eg. 90) and operator (>=) and close the bracket.
5. Click OK and the counted numbers gets displayed on the selected cell.
Example 3:
COUNTIF also help you to count the number of students if the values is in non numeric form . For instance if you want to find the number of students who have submitted the project from data given in handout 1, follow the given steps:
1. Open function wizard and select COUNTIF following the steps given in the previous examples
3. Type the name of function - COUNTIF after the “=” sign in formula text box and type open parenthesis after the name of function . Now select the range of cells or type it parenthesis of which the instances need to be counted (Eg: corresponding to english marks ) which has a numerical value. Note: Column H does not contain the numerical value.
4. Give your condition in criteria section and mention the text within double quotes (Eg : “PROJECT
REPORT” ) and close the bracket.
5. Click OK and the counted numbers gets displayed on the selected cell.
COUNT A FUNCTION
This function counts the number of values in a given list of argument . The difference between COUNT and COUNTA is "COUNT" only counts numbers, while CountA everything else and numbers as well.
Formula has to given as :
= COUNTA (range)
Where, range refers to the range of cells that you want to count the data. This can also count continuous range of cells.
For Example :
If you want to know the number of students in the class , you can use COUNT function and apply range with any numeric field as discussed. The Count function work with numeric values only , if you want to know the names of female and male students in a class then you have to use COUNTA function and prescribe a range with non numeric field to count number of female and male students in the class .
Example 1:
To count the number of students in class by selecting the names of students from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Goto Insert Menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘STATISTICAL’ category under the category and search for function ‘COUNTA’ in function list.
3. Type the name of function - COUNTA after the “=” sign in formula text box and type open parenthesis after the name of function . Now select the range of cells or type it in parenthesis of which the instances need to be counted (Eg: corresponding to number of name of students ) which has a numerical value.
4. After giving range , close the parenthesis .
5. Click OK and the counted numbers of values in the given range gets displayed on the selected cell.
OR
There is another way of using COUNTA , It is the shortest way to use the formula :
Follow the following steps :
1. Select the data range with the help of mouse .
2. Go To the status bar and choose the area where the some common formula are depicted .
3. Right click on the area of status bar and choose the COUNTA option , you will be able to see the number of values in range selected by you with the name of function.
4. Select the function COUNTA and click on it , you will be able to know the number of values in given range(for eg. Number of students in a class)
COUNTIF FUNCTION
COUNTIF function is used to counts the number of times a specified value as a criteria has been repeated within the specified range of cells . For example you have english marks of your class of 40 students and you want to know the number of students who have secured marks equal to 90 , then countif function will help you to find exact number of students who have secured 90 in easy and quick manner.
Formula has to given as :
= COUNTIF (range,criteria)
Where,
range - the range of cells that you want to count the data. There has to be an ongoing area.
Criteria - a condition that must be met for cell counting.
Criteria can be any of the following :
= Equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
<> not equal to
All these operators must be enclosed in quotation marks and the ampersand (&) is needed in to add as as to combine values and operator . By default the formula will use the equal to “=” operator with value in criteria.
Example 1:
If you want to find the number of students who secured 90 marks in english from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Go to Insert menu and select Function from drop down Menu , a Function Wizard box will appear. Select the ‘MATHEMATICAL’ category under the category and search for function ‘COUNTIF’ in function list.
3. Type the name of function - COUNTIF after the “=” sign in formula text box and type open parenthesis(bracket) after the name of function . Fields range and criteria appears as highlighted below. Now select the range of cells or type it inside the parenthesis of which the instances need to be counted (Eg: corresponding to english marks ) which has a numerical value. The selected range appears in the formula.
1. Give your condition in criteria section (in this example, as you want to find students scored equal to 90, no need to use any operator since by default it will take the “=” operator) and close the bracket in the formula section.
2. Click OK and the counted numbers gets displayed on the selected cell.
Example 2:
If you want to find the number of students who secured above 90 marks in english from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Go to Insert menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘MATHEMATICAL’ category under the category and search for function ‘COUNTIF’ in function list.
3. As in the previous example, type the function COUNTIF and select the range of values to checked (Example: English marks column)
4. Give your condition in criteria section and mention the operator (Use “>=” as you need to find greater than or equal to 90) and use “&” to combine both value (Eg. 90) and operator (>=) and close the bracket.
5. Click OK and the counted numbers gets displayed on the selected cell.
Example 3:
COUNTIF also help you to count the number of students if the values is in non numeric form . For instance if you want to find the number of students who have submitted the project from data given in handout 1, follow the given steps:
1. Open function wizard and select COUNTIF following the steps given in the previous examples
3. Type the name of function - COUNTIF after the “=” sign in formula text box and type open parenthesis after the name of function . Now select the range of cells or type it parenthesis of which the instances need to be counted (Eg: corresponding to english marks ) which has a numerical value. Note: Column H does not contain the numerical value.
4. Give your condition in criteria section and mention the text within double quotes (Eg : “PROJECT
REPORT” ) and close the bracket.
5. Click OK and the counted numbers gets displayed on the selected cell.
COUNT A FUNCTION
This function counts the number of values in a given list of argument . The difference between COUNT and COUNTA is "COUNT" only counts numbers, while CountA everything else and numbers as well.
Formula has to given as :
= COUNTA (range)
Where, range refers to the range of cells that you want to count the data. This can also count continuous range of cells.
For Example :
If you want to know the number of students in the class , you can use COUNT function and apply range with any numeric field as discussed. The Count function work with numeric values only , if you want to know the names of female and male students in a class then you have to use COUNTA function and prescribe a range with non numeric field to count number of female and male students in the class .
Example 1:
To count the number of students in class by selecting the names of students from data given in handout 1, follow the given steps:
1. Select the cell where you want the solution to be displayed.
2. Goto Insert Menu and select Function from drop down menu , a Function Wizard box will appear. Select the ‘STATISTICAL’ category under the category and search for function ‘COUNTA’ in function list.
3. Type the name of function - COUNTA after the “=” sign in formula text box and type open parenthesis after the name of function . Now select the range of cells or type it in parenthesis of which the instances need to be counted (Eg: corresponding to number of name of students ) which has a numerical value.
4. After giving range , close the parenthesis .
5. Click OK and the counted numbers of values in the given range gets displayed on the selected cell.
OR
There is another way of using COUNTA , It is the shortest way to use the formula :
Follow the following steps :
1. Select the data range with the help of mouse .
2. Go To the status bar and choose the area where the some common formula are depicted .
3. Right click on the area of status bar and choose the COUNTA option , you will be able to see the number of values in range selected by you with the name of function.
4. Select the function COUNTA and click on it , you will be able to know the number of values in given range(for eg. Number of students in a class)
No comments:
Post a Comment