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
In an “IF function” there will be two output i.e. TRUE or FALSE since either the statement will be “TRUE” or “FALSE”. If the statement is matching or correct, then output will be “TRUE” or if the statement is not matching or not correct then the output will be “FALSE
MAX function is used to get the largest number in range or list of values. MAX function has one required argument i.e. number1
Have you ever got into situation in office where you need to count the cells in Excel sheet with specific color? If yes then you can use following code which counts the number of cells…
Learn to reduce excel file size. Tips include removing unnecessary formatting, compressing images, using formulas instead of hardcoded data, and optimizing pivot tables. Clearing unused cells and minimizing data ranges also help. Lastly, consider saving as binary or using third-party add-ins for further compression.
LEFT function is used for extracting the “Left Most” characters from the available string. The output of the function returns the extracted characters in new cell
Index Match in Excel are powerful tools for advanced lookups. They’re popular because they’re incredibly flexible. You can use them to search horizontally or vertically, perform two-way searches, find values to the left of your search criteria, handle case-sensitive searches, and even search using multiple conditions. If you want to improve your Excel skills, mastering INDEX and MATCH is essential. Below, you’ll find numerous examples to help you grasp how they function.