COUNTIF Excel Function is also one of the most used function in excel. This helps the user to calculate the number of counts based on single logic given by the user. You can use this function for multiple purposes like:
1. Counting the occurrence in an excel range if meets a criteria i.e. exact value matching, partial value matching, more than or less than of a numeric value
2. Check whether one value exist in other range
3. Count the occurrence if equal to either of values etc.
Below is the syntax for COUNTIF Function in excel:
1. =COUNTIF(Range,Criteria)
– Range: It is an excel range in which you want to count the occurrence of your criteria.
– Criteria: This is a criteria basis on occurrence in the range being counted
COUNTIF can be used for multiple purposes like
– When you want to count the occurrence of single logic in an excel range
– Also you can use to verify whether one cell value exist in other range. It helps to match one excel range to another range
– You can also count of a value when a criteria in excel range matches, contains, not equal to etc. via wild characters (will discuss this further)
– You can use this function as a logical argument to check if True or False. This can help you in conditional formatting
Things To Remember
– Works with Single Logic and one range (either row or column)
– COUNTIFS can be used for multiple logics
– If your excel range is linked to other workbook, then this function will return #value error when workbook is closed
– Double quotes (” “) should be used when you are using wild characters or not linking the Criteria to Proper Text or Value instead of Excel Cell
Below we are using sales data which contains revenue details for different clients. So here we are going to count the values for particular state.
Suppose you want to count the sales made to clients who belong to state”VA”. So you need to write COUNTIF formula as
=COUNTIF(D:D,"VA")
Here my state details are available in Column D. That’s why I selected D:D range and then typed “VA” as text. So function will check the range and count the occurrence of “VA” state in the given range and retunr the number of count.
You can link the criteria to Cell as shown in below image and get the results:
Now suppose you want to count the cells which are not equal to blank cells or any other value in an excel range. You may use this syntax:
=COUNTIF($D:$D,"<>"&"")
Here “<>” is a wild character which is being used for “Not Equal” Criteria and “&” is used to connect one string to another. So here I am using & to connect “<>” with my criteria “” (Non Blank Cells). You can use any string or value or cell to count cells in a range when not equal to. Below are few more criteria examples:
– Count when not equal to
“<>”&””
– Count when not equal to value available in cell G2
“<>”&G2
– Count when not equal to State “VA”
“<>”&”VA”
Now suppose you want to count the cells which are more than a numeric value in an excel range. You may use this syntax. Here we are counting the clients who paid more than $50 by checking Column B
=COUNTIF($B:$B,">"&50)
Similar to above example, we are using wild character “>” when more than. You can use different wild characters for counting the numbers in an excel range
– Count when more than 50
“>”&50
– Count when more than to value available in cell G8
“>”&G8
– Count when less than 50
“<”&50
– Count when less than to value available in cell G8
“<”&G8
You can use combination of “>=” or “<=” for when value equals & more than or equals & less than respectively
Now suppose you want to count the cells when cell contains particular string in an excel range. You may use this syntax. Here we are counting the clients whose name contains “Tamm” Column A
=COUNTIF($A:$A,"*"&"Tamm"&"*")
Similar to above example, we are using wild character “*”&Value/Cell&”*”. This is used to check the range, if contains. Here are few more syntax which are mostly used
– Count when cell value begins with
“Tamm”&”*”
G11&”*”
– Count when cell value ends with
“*”&“Tamm”
“*”&G11
– Count when cell contains
“*”&“Tamm”&”*”
“*”&G11&”*”
Hope you liked this article. Please comment below for any queries and yes, please do not forget to follow us or subscribe us as below
How to Change TEXT into DATE Format Microsoft Excel “DATEVALUE” is the very useful where dates are available as TEXT and need to convert them to proper DATE format. DATEVALUE function helps to convert the…
Merge Cells in Excel Merge cells is to combine multiple cells into one cell which can further be used for giving title to the report or header to the column. It helps to create clean…
Excel Function ISERROR Microsoft Excel “ISERROR Function” is a Logical Function and it is used to check if cell contains any “ERROR”. “ISERROR Function” is used as a test to validate if cell contains any…
COUNTBLANK function is used to get the total count of Blank or Empty cell in range.
COUNTBLANK Function has one required argument i.e. range.
Excel Function DATE While using Microsoft Excel, you must have got the situation where you need to use the Date function. Date function is one of the common and most used function in Excel that…
Microsoft Excel “TODAY” function is used to get the current Date. It is very useful function and can be used in many ways. “TODAY Function” does not have any argument that makes this easy to apply and implement.