INTRODUCTION
COUNTIFS FUNCTION comes under the STATISTICAL FUNCTIONS category in Excel.
It is an extension to the COUNTIF FUNCTION. As we know that we can put a condition in the COUNTIF FUNCTION. COUNTIFS goes another step forwards and lets us mention many conditions. If you refere to previous article, there was a case when we need to write the function twice to get the things done. Here , we need not to use the formula twice even if there are more than one conditions.
COUNTIFS function , as the name suggests, does the counting job in Excel , just like the COUNT FUNCTION and COUNTIF FUNCTION with the difference that we can provide many condition in COUNTIFS FUNCTION which cells to count.
The other difference is that it is free from any type of data specification. It just needs the value what we look for.
COUNTIFS FUNCTION is very helpful when we need to use some nested functions to make a check on the number of cells containing the number or number type of data.
One more usage of this function will be when we start learning 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.
Let us learn ,how to use this function.
PURPOSE OF COUNTIFS FUNCTION IN EXCEL
COUNTIFS FUNCTION returns the total number of cells from a given data set or range which fulfills all the given criteria.
PREREQUISITES TO LEARN COUNTIFS 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.
Helpful links for the prerequisites mentioned above
What Excel does? How to use formula in Excel?
SYNTAX: COUNTIFS FUNCTION
The Syntax for the function is
=COUNTIFS(RANGE, CRITERIA , RANGE2, CRITERIA 2…….)
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.
RANGE2 Range 2 is the next range or the same range on which we want to apply next logic.
CRITERIA2 The criteria for the Range 2.
NOTE* CRITERIA NEEDS TO BE GIVEN IN THE “”.
EXAMPLE:COUNTIFS 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 COUNTIFS 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 150
and less than 150. And we’ll try one of the same example
which needed two conditions.
The cells which has value more than 100 and less than 200.
STEPS TO USE COUNTIFS FUNCTION-EXAMPLE
STEPS:
1. Place the cursor in the cell and type the following function
CRITERIA 1: >100 and <150
=COUNTIFS(C5:C21,”>100″,C5:C21,”<150″)
2. Press ENTER, the answer will appear as 1.
3. Place the cursor in the cell and type the following function
CRITERIA 2: >100 AND <200
=COUNTIFS(C5:C21,”>100″,C5:C21,”<200″)
4. Press ENTER, the answer will appear as 1.
The answers can be checked manually. We find that all answers are correct.
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.
CRITERIA 2: >100 AND <200
=COUNTIF(C5:C21,”AND(>100,<200)”)
CRITERIA 1: PLACE ENDING WITH “DON”
3.The answers can be checked manually. We find that all answers are correct.
GENERALIZED STEPS TO USE COUNTIFS function
HERE ARE THE STEPS TO USE COUNT FUNCTION
- Type the function
- =COUNTIF(RANGE, CRITERIA) in the cell where we want the result.
- Press ENTER and the result will appear.
