How to use COUNTIF formula in Excel

 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) 

COUNTIF FORMULA

 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) 

EXAMPLE 2 COUNTIF

 

You can also see well explained video here

Leave a Reply