Which of the following function returns the number of cells containing values?
|
The Microsoft Excel COUNTIF function counts the number of cells in a range, that meets a given criteria. Show
The COUNTIF function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the COUNTIF function can be entered as part of a formula in a cell of a worksheet. If you wish to apply multiple criteria, try using the COUNTIFS function.  If you want to follow along with this tutorial, download the example spreadsheet. Download Example SyntaxThe syntax for the COUNTIF function in Microsoft Excel is: COUNTIF( range, criteria ) Parameters or ArgumentsrangeThe range of cells that you want to count based on the criteria.criteriaThe criteria used to determine which cells to count.ReturnsThe COUNTIF function returns a numeric value. Applies To
Type of Function
Example (as Worksheet Function)Let's look at some Excel COUNTIF function examples and explore how to use the COUNTIF function as a worksheet function in Microsoft Excel: Based on the Excel spreadsheet above, the following COUNTIF examples would return: =COUNTIF(A2:A7, D2) Result: 1 =COUNTIF(A:A, D2) Result: 1 =COUNTIF(A2:A7, ">=2001") Result: 4 Using Named RangesYou can also use a named range in the COUNTIF function. A named range is a descriptive name for a collection of cells or range in a worksheet. If you are unsure of how to setup a named range in your spreadsheet, read our tutorial on Adding a Named Range. For example, we've created a named range called family that refers to column A in Sheet 1. Then we've entered the following data in Excel: Based on the Excel spreadsheet above: =COUNTIF(family, D2) Result: 1 =COUNTIF(family, ">=2001") Result: 4 To view named ranges: Under the Insert menu, select Name > Define. Frequently Asked QuestionsQuestion: I'm trying to use COUNTIF on a selection of cells (not necessarily one solid range), and the syntax of the function does not allow that. Is there another way to do this? Here's an example of what I'd like to be able to do: =COUNTIF(A2,A5,F6,G9,">0") Answer: Unfortunately, the COUNTIF function does not support multiple ranges. However, you could try summing multiple COUNTIFs. For example: =SUM(COUNTIF(A2,">0"),COUNTIF(A5,">0"),COUNTIF(F6,">0"),COUNTIF(G9,">0")) OR =COUNTIF(A2,">0")+COUNTIF(A5,">0")+COUNTIF(F6,">0")+COUNTIF(G9,">0") Question: I am using the COUNTIF function and I would like to make the criteria equal to a cell. For example: =COUNTIF(C4:C19,">=2/26/04") I want to replace 2/26/04 with cell A1. How do I do this? Answer: To use a cell reference in the criteria, you could do the following: =COUNTIF(C4:C19,">="&A1) Question:I would like to do the following: =COUNTIF(ABS(A1:A10),">0") i.e. count the number of values in the range A1:A10 that have a non-zero magnitude. The syntax I tried does not work. Could you help? Excel is a widely used data management tool that helps us analyze and interpret data. It is underappreciated for the wide variety of tasks that can be performed on the application. Excel has a versatile library of built-functions, making it a very powerful tool. Hence making it a popular one too. In this article, we will be discussing the function COUNT in Excel. As the name suggests, the ‘COUNT’ function helps us count cells in Excel. But that’s not it! There are five variations of COUNT in Excel, which helps us perform numerous counting tasks that we will be exploring in this article. What is COUNT in Excel?The COUNT function is generally used to count the number of cells in Excel or array of numbers. Example: To count the numbers between A1 and A20, you may enter the following formula: =COUNT(A1:A20). For example, if the range contains three cells containing numbers, the result is 3. There are two different ways to use COUNT in Excel. The syntax is as follows: COUNT(value1,[value2],…)The parameters of the COUNT function include comma-separated values. This function returns the total count of numeric values specified. Usage: Here we specify multiple values as the parameter and the COUNT function finds the total count of values in it. NOTE: This function works only on numeric data. Hence, it will not find the count of text values if specified. COUNT(range)This function is used to count the number of cells in a specified range. It can be used to quantify data. As we can see, using =COUNT(A2: A6), we get the count of cells in the range A2 to A6. This function works only on numeric data. Now that we have understood how to use the function COUNT in Excel let’s look at the next function - COUNTA. PCP in Business AnalysisIn partnership with Purdue UniversityVIEW COURSEWhat Does the Function COUNTA Do in Excel?This function counts cells with data, i.e., non-empty values within a range. The syntax is as follows: =COUNTA(value1,[value2],…)The values can be any range too. For example A1:A10 Usage: As shown above, =COUNTA(E2: E6) counts cells with data(non-empty cells) in the range E2 to E6. What Is the Difference Between COUNT and COUNTA in Excel?COUNT calculates the count of cells with numeric values. In comparison, COUNTA finds the count of cells with non-empty data. How Do You Use COUNTBLANK?As the name implies, COUNTBLANK is a simple function that counts the number of blank or empty cells. The syntax is as follows: =COUNTBLANK(range)The COUNTBLANK function works on a specified range as a parameter. Usage: Looking at the example shown, COUNTBLANK helps count cells that are left blank/empty. We can use this function to find the entries that are left blank. How Do You Use COUNTIF?The COUNTIF function counts cells that match specific criteria. The syntax is as follows: =COUNTIF(range,criteria)The two arguments here are:
This function works on the specified range, counting the cells that match the criteria or condition. Usage:
For example: We are using the formula =COUNTIF(C2: C6, “Tianjin”) to check how many locations match the city “Tianjin”.
Counting cells that only contain text is simple. We use ‘*’ as the condition. The ‘*’ or asterisk symbol helps find any number of characters. In the following demo, you will see how the wildcard ‘*’ matches any number of characters. The function =COUNTIF(D2: E12,”*”) executes over the range D2 to E12 and counts cells that contain text. If you want a partial match, you can use wildcard characters to count cells that contain a particular word or letters. Look at the example below: The formula =COUNTIF(C2: C12, “*HUBEI*”) counts the cells that have the value “HUBEI” in the range C2 to C12. NOTE: Here, the asterisk symbol helps find cells with any sequence of leading or trailing characters. Also, this does not apply to boolean values.
For example: =COUNTIF(E2:E6,">=60") Here, we have used the COUNTIF function to find the number of people over the age of 60 affected by COVID. We have taken the range of ages from cells E2 to E6. The criteria being that the value must be greater than or equal to 60. NOTE: The criteria MUST be specified within quotes. Similarly, you can use the following logical operations. Logical operation Formula Example Description Less than “<” =COUNTIF(C1:C10,”<20”) Count if the values in cells are lesser than 20 Less than or equal to “<=” =COUNTIF(C1:C10,”<=20”) Count if the values in cells are lesser than or equal to 20 Greater than “>” =COUNTIF(C1:C10,”>20”) Count if the values in cells are greater than 20 Greater than or equal to “>=” =COUNTIF(C1:C10,”>=20”) Count if the values in cells are greater than or equal to 20 Equal to “=” =COUNTIF(C1:C10,”=20”) Count if the values in cells are equal to 20 Not equal to “!=” =COUNTIF(C1:C10,”<>20”) Count if the values in cells are not equal to 20 Free Course: Introduction to MS ExcelMaster the Fundamentals of MS ExcelEnroll NowHow Do You Use COUNTIFS?This function is a plural counterpart of the COUNTIF function. It calculates the count of cells upon meeting multiple criteria. The syntax is as follows:
Here, range1 maps to the criteria1, range2 maps to criteria2, and so on. The range1 and criteria1 are required arguments while the rest in square brackets are optional.
Multiple range-criteria sets can be added, and they must be comma-separated. We will demonstrate the usage as follows. The formula =COUNTIFS(B2:B12,">2/1/2020",D2:D12,"France") is used on two criteria. It checks if the reporting date is after 2/1/2020 AND if the country of the deceased individual is France. NOTE: The COUNTIFS function follows AND logic. It counts cells only if both or ALL conditions are true. More About COUNTIFThere are various numbers of tasks that can be performed using COUNTIF and COUNTIFS.
You can use =COUNTIF(A1: A10, A1). This will be demonstrated below. Using =COUNTIF(F2: F12, F2), we find the total count of cells where the cell containing “male” gender has been repeated.
We can do so by using an IF with COUNTIF. Look at the following example. The COUNTIF function executes first and finds the count of cells within the range A2 to A12, where the value in cell A3 (which is 2) has appeared. If the count is more than 1, duplicate values exist, and the result will be displayed as “DUPLICATE”. Else it displays “UNIQUE”. Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now! ConclusionWe hope this article has given you a strong understanding of how various functions are used to COUNT in Excel. The function may seem to perform simple calculations. But when you combine them with other Excel functions, you will be amazed by how powerful Excel is in getting meaning out of massive datasets. Do you have any questions related to this article? If so, please mention it in the comments section and our experts will get back to you at the earliest. You can also go the extra mile and enhance your Excel skills by enrolling in the Business Analytics with Excel Free Course offered by Simplilearn. About the AuthorShruti is an engineer and a technophile. She works on several trending technologies. Her hobbies include reading, dancing and learning new languages. Currently, she is learning the Japanese language. Which function returns the number of cells?The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
What is the Excel function that returns the number of values of the selected cells?Use AutoSum
Excel returns the count of the numeric values in the range in a cell adjacent to the range you selected. Generally, this result is displayed in a cell to the right for a horizontal range or in a cell below for a vertical range.
Which function returns the total number of cells or arguments which have some value in it *?You use the COUNT function in Excel to count the number of cells that contain numerical values. The syntax of the Excel COUNT function is as follows: COUNT(value1, [value2], …) Where value1, value2, etc.
Which function returns the number of values in a column?The COLUMN function returns the column number of the given cell reference. For example, the formula =COLUMN(D10) returns 4, because column D is the fourth column.
|
