PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
EXCEL FUNCTIONS-COUNTIF
INTRODUCTION
COUNTIF FUNCTION comes under the STATISTICAL FUNCTIONS category in Excel.
It is an extension to the COUNT FUNCTION.
COUNTIF function , as the name suggests, does the counting job in Excel , just like the COUNT FUNCTION with the difference that we can provide condition in COUNTIF FUNCTION which cells to count.
The other difference is that it works independently of the type of data which means that it would work for numbers as well as text or any other data type.
COUNTIF FUNCTION is very helpful when we need to use any nested functions to make a check on the number of cells containing the number or number type of data.
This function is also very helpful when we are programming VBA. We’ll be needing a lot of functions to know the status of the sheet so that we can decide what to do further. This function is one of those functions which are helpful.
Let us learn ,how to use this function.
PURPOSE OF COUNTIF FUNCTION IN EXCEL
COUNTIF FUNCTION returns the total number of cells from a given data set or range which fulfill a particular given criteria.
PREREQUISITES TO LEARN COUNTIF FUNCTION
THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.
- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the STATISTICAL terms is an advantage for the use of such formulas.
- Of course, Excel software.
SYNTAX: COUNTIF FUNCTION
The Syntax for the function is
=COUNTIF(RANGE, CRITERIA)
RANGE Given range which needs to be scanned to find out the number of cells which fulfill the criteria
CRITERIA The criteria on the basis of which , cells will be counted.
NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.
EXAMPLE:COUNTIF FUNCTION IN EXCEL
DATA SAMPLE
Let us take a mixed data consisting of numbers, dates, numbers as text and text etc. so that we can check the working of function COUNTIF by giving different criteria.
The data used is shown below.
| 12 |
| 134 |
| 234 |
| LONDON |
| 34 |
| PARIS |
| 34 |
| DELHI |
| 234 |
| OSLO |
| 2 |
| OTAWA |
| 234 |
| 34 |
| 2 |
| TOKYO |
| 234 |
We’ll find out the cells which has value more than 100
and
The cells which has value more than 100 and less than 200.
And finally using a wildcard will find the place which has “don” at the end of its name and three letters before “don”.
STEPS TO USE COUNTIF FUNCTION-EXAMPLE
STEPS:
1. Place the cursor in the cell and type the following function
CRITERIA 1: >100
=COUNTIF(C5:C21,”>100″)
2. Press ENTER, the answer will appear as 9.
3. Place the cursor in the cell and type the following function
CRITERIA 2: >100 AND <200
=COUNTIF(C5:C21,”>100″)-COUNTIF(C5:C21,”>200″)
*If there are two conditions, its better to make a logic like the one we made. It fulfills the conditions but the condition is single.
4. Press ENTER, the answer will appear as 1.
5. Place the cursor in the cell and type the following function
CRITERIA 3: PLACE ENDING WITH “DON”
6. Press ENTER, the answer will appear as 1.
The answers can be checked manually. We find that all answers are correct.
generalized STEPS TO USE COUNTIF function
- Type the function
- =COUNTIF(RANGE, CRITERIA) in the cell where we want the result.
- Press ENTER and the result will appear.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.