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
Watch Video: Rand and Randbetween Excel Functions Generate Random Numbers using Excel Functions We have got many instances where we needed to generate a random database or values. “RAND function” is very useful for users…
Microsoft Excel lets you share your spreadsheets with others, but sometimes your co-workers might edit the wrong column by mistake. To avoid this, you can lock specific columns. In this guide, we’ll show you how…
Watch: How to use SUMIF & SUMIFS Function in Excel? What is SUMIF Function? The SUMIF function in Excel adds up values in a range of cells that meet certain criteria. Click here to Read Full…
Introduction Welcome to this comprehensive guide on using Power Query in Excel to split data efficiently. In this tutorial, we’ll walk you through the step-by-step process of splitting columns, specifically focusing on splitting the name…
AVERAGE function is used to get the average of numbers. Function applies formula i.e. average = Sum of all values / (Divided by) number of items.
INDIRECT function is used to convert the text/string into cell reference. Function provides output as the value of that cell reference.