Table of Contents
You might regularly need to figure out how many cells meet certain requirements when working with data in Excel. For instance, you might want to know how many sales exceed a given amount, how many products are out of stock, or how many of your consumers come from a particular area. The COUNT function and COUNTIF function in Excel are two effective Excel tools that can aid you in doing these tasks.
Microsoft Excel offers several methods for counting various cell types, including blank or non-blank cells, cells with numbers, dates, or text values, cells containing particular words or characters, etc.
We’ll focus on COUNT function and COUNTIF function in Excel ahead in this post because they can be used to count the number of cells that match certain criteria. Additionally, we’ll discover some insights into advanced Excel online classes.
What is the COUNT function?
Excel’s COUNT function counts both the number of cells with numbers and the number of arguments in the list of arguments.
All numbers—positive, negative, percentages, dates, times, fractions, and algorithms with return values expressed as numbers—are counted.
The function ignores all other cells, including blank cells and cells with non-numerical values like text, and only counts cells that have numerical values.
The function can receive a number of parameters of the type value1, value2, value3, etc.
Usage of the COUNT Function in Excel
Syntax
COUNT(value1, [value2], ...)Following are the arguments for the COUNT function syntax:
value1 is necessary. The first thing is the cell reference, or range, that you want to count numbers within.
value 2, optional. Additional items, cell references, or ranges totaling a maximum of 255 are to be used to count numbers.
Example
Let’s take a look at an illustration of how to use the COUNT function to count the numbers within a certain range. as displayed in the table below.
Enter the formula
=COUNT(B3:B8) in cell B10 after selecting it and pressing “Enter”.
The result is displayed above. There are only three numerical numbers in the range B3:B8. As a result, the COUNT function outputs 3.
What is the COUNTIF Function in Excel?
Based on pre-established parameters, Excel’s COUNTIF function counts the number of cells in a range.
Cells with dates, numbers, or text are to be counted using this technique. For instance, COUNTIF (A1:A10, “course”) counts the number of cells that have the word “course” in them.
How do I use the COUNTIF Function?
syntax
=COUNTIF (range, criteria)It will require the following necessary arguments:
range: denotes the range of values to which the criteria will be applied.
Criteria: It displays the restriction imposed on the range of values.
As a result, the values that satisfy the requirements are returned.
The COUNTIF formula produces a positive number that can be zero or not zero.
Let’s look at a few applications to better grasp how the COUNTIF function in Excel functions.
1. With the Given Value, Count Values
The cells A2 through A7 in the following table display a list of numbers. Look up the values that correspond to the number “33” in the provided cell range.
The formula reads:
("=COUNTIF (A2:A7,33)")"
The integer “33” is used in this case as the formula’s condition. The formula looks for values that match the number “33” in the range of cells A2 to A7. So There is just one such number in the range that meets the requirement. As a result, “1” is the value returned by the COUNTIF function. Cell A8 displays the outcome.
2. Count Numbers Less Than the Assigned Number
The following table includes a list of the data located in cells A12 through A17. Look for values less than “50” in the cells in the specified range.
The formula reads:
“=COUNTIF(A12:A17,“<50”)”
In this case, the formula’s requirement is “50.” The COUNTIF formula verifies that the cell range is less than 50 and meets the requirement. There are only four values in the range that are less than 50. As a result, “4” is the outcome that this function gives. Cell A18 displays the outcome.
3. Values to Count Using the Given Text Value
Below is a table with a list of the information found in cells A22 through A27. The cells that contain the text value “john” are counted.
The formula reads:
"=COUNTIF(A22:A27, "john")"
The term “John” serves as the criterion in this case. The range of cells that satisfy the specified criterion is checked by the COUNTIF formula.
Only one cell in the specified range matches the text value “john” exactly. As a result, cell A28 in the output is set to “1”.
4. List the negative numbers.
The table below provides a list of data in the range of cells A32 to A37. Search the cell range for negative values—those less than zero.
The formula reads:
"=COUNTIF(A32:A37, "0")"
Here, the formula’s condition is that the input is less than zero. The COUNTIF algorithm will now locate and count the numbers in the specified range that have negative values. In the range provided, there are three such numbers. As a result, cell A38 in the output shows “3”.
5. count of zero values
The table below provides a list of data in the range of cells A42 to A47. Cells with the value “0” are counted in the range.
The formula reads:
“=COUNTIF(A42:A47,0)”
The formula’s condition is set to “0” in this instance. The COUNTIF formula will now recognize and count the numbers that have a value of zero. The range has two zeros. Since “2” is presented in cell A48 as the result, it is returned.
Multiple criteria COUNTIF function in Excel: COUNTIFS
To count the number of cells in a range that satisfy a single condition, use Excel’s COUNTIF function.
CONTIF Function can be used to count Cells that meet several criteria, which accepts multiple criteria as inputs and only counts the cells when each condition is TRUE.
Syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]...),where [criteria_range2, criteria2] are optional additional ranges and their corresponding criteria, and [criteria_range1, criteria1] is the initial range to which the first condition (criteria1) shall be applied. Your formulas can contain up to 127 ranges or criteria pairs.
COUNTIFS with AND logic
You can use one of the following techniques to count cells that satisfy several criteria with AND logic, which requires that each criterion be true:
Use COUNTIFS, a function that accepts some conditions as inputs. =COUNTIFS(range1, criteria1, range2, criteria2,…) is the syntax.
For instance, you may use the following formula to determine the number of cells in the ranges A1 to A10 that have the word “apple” and B1 to B10 that have a value larger than 10.
=COUNTIFS(A1:A10,"apple,"B1:B10,">10")combine the results of several COUNTIF procedures.
Syntax:
=COUNTIF(range1, criteria1) * COUNTIF(range2, criteria2) *... Use this formula, for instance, to count the number of cells in ranges A1 to A10 that contain the word “apple” and B1 to B10 that have a value greater than 10 to count the number of such cells:
=COUNTIF(A1:A10,"apple") * COUNTIF(B1:B10,">10"COUNTIFS with OR logic
Use one of the following techniques to count cells that satisfy several criteria using OR logic, where any of the criteria can be true:
you can use several COUNTIF functions and combine them. =COUNTIF (range1, criteria1) + COUNTIF (range2, criteria2) +… Use this formula, for instance, to count the number of cells in the range A1–A10 that contain the words “apple” or “banana”:
equals COUNTIF(A1:A10,”apple”) + COUNTIF(A1:A10,”banana”).
Utilize a single instance of COUNTIF with an array constant as the criteria.
syntax
=SUM(COUNTIF(range, "criteria1, criteria2,...")) For instance, you may use the following formula to determine how many cells in the range A1:A10 contain the words “apple,” “banana,” or “lemon”:
=SUM(COUNTIF(A1:A10,{"apple","banana","lemon"}))Use array constants with the SUM and COUNTIFS functions for the criteria ranges and criteria. =SUM(COUNTIFS(criteria_range1, criteria1, criteria2,…, criteria_range2, criteria3; crit4;…)) is the syntax.
The formula =SUM(COUNTIFS(A1:A10, “apple”, “banana”, B1:B10,”>10″; “5”) can be used to count cells that include “apple” or “banana” and have a value greater than 10 or less than 5 in the range B1:B10.
Advanced excel course
You can learn how to use Excel with greater skill and efficiency by taking an advanced Excel course.
Advanced formulae, data analysis, data visualization, dashboard development, financial modeling, macros, functions, and more are among the topics it covers.
Hence You may enhance your abilities, productivity, and career chances by taking an advanced Excel course.
Henry Harvin academy
Henry Harvin Academy in India provides one of the most reputable Advanced Excel Courses Online. You can find a supportive group in Henry Harvin’s advanced Excel course. The entire training is hands-on for the students.
My experience has shown that Henry Harvin empowers learning through students. Henry Harvin offers online Excel classes with certificates that train you from the fundamentals up to the advanced level. Therefore Henry Harvin is one of the best options to learn an advanced Excel course.
Conclusion
In this blog post, we learned how to count cells that satisfy one or more criteria while also using the COUNT and COUNTIF functions in Excel. The COUNTIF function in Excel is a potent tool for data analysis and pattern recognition. Check out the following video to learn more about the Excel course: We sincerely hope you enjoyed and learned from this blog post.
given is the tutorial of an advanced Excel training course by Henry Harvin
Recommended reads
- Top 25 Microsoft Excel Formulas you Should Know
- online-advanced-excel-courses
- benefits-of-taking-a-course-in-advanced-excel
FAQ’s
Q1. What is the COUNT function in Excel?
Ans. Excel’s COUNT function is a statistical tool that counts both the number of cells with numbers and the number of arguments in the list of arguments. Up to 255 arguments—individual values, cell references, or ranges—can be passed to it. It disregards empty cells, logical values, errors, and text values.
Q2. What is the COUNTIF function in Excel?
Ans. The COUNTIF function in Excel counts the number of cells that satisfy a specific criterion. A number, text, cell reference, expression, or another Excel function can serve as the criterion.
Q3. What is the syntax for the COUNTIF function in Excel?
Ans. The syntax of the COUNTIF function is: =COUNTIF (range, criteria)
Q4. What is the syntax for the COUNT Function in Excel?
Ans. The syntax of the COUNT Function in Excel is: COUNT(value1, [value2], …)
Q.5. How can I distinct in the usage of COUNT and COUNTIF functions in Excel?
Ans: If your purpose is to know how many cells contain numeric data (numbers) in a range, then use the Count function. But if your purpose is to count the number of cells that meet a specific condition, you must use the COUNTIF function.
.webp)
