Welcome to Excel Avon
Summary
In this post I will show you how to use COUNTIF formula in excel. “COUNTIF” is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text
Formula Syntax
=COUNT(Range,Criteria)
The “COUNTIF” function uses the following arguments:
-
- Range– Here range refer to cells for which you want the cells count for specific condition.
- Criteria-Here criteria refers to the condition for which you want the cells count.
Note :
-
- COUNTIF returns incorrect results when used to match strings longer than 255 characters.
- COUNTIF requires a range, you can’t substitute an array.
- COUNTIF will return a #VALUE error when referencing another workbook that is closed.
- Cell references in criteria are not enclosed in quotes, i.e. “<“&A1
How to use COUNTIF formula in Excel
Example-1
So for example I will use student & total mark range so for numerical criteria. So in 1st example I want to calculate total numbers of person per city so I will use =COUNTIF(RANGE,CRITERIA)
=COUNTIF(B2:B22,D4)
Arguments
B2:B22 is range & D4 is Criteria
I will use for next cities so I will lock the formula range Press “F4” so it will be locked and drag it down so by “COUNTIF” I have calculated person per city number of person per city.
(according attached below image)
Example-2
In 2nd Example for example I am using list of student & total mark so I want to know how many student are passed in Exam so I will make a criteria, If the mark is equal or greater to 33 then students will be passed so formula is =COUNTIF(RANGE,>=CRITERIA)
=COUNT(B2:B3,”>=33)
So this is calculating how many student are passed so this way you can use a numerical criteria.
(according attached below image)
You can also see well explained video here