Lesson 22: How to Count Cells with COUNTA Show
/en/excel-tips/use-the-proper-function-to-capitalize-names-in-excel/content/ How to count cells with COUNTAYou may already know Excel can perform calculations with numbers. But did you know it can also perform calculations with other types of information? One of the simplest examples of this is the COUNTA function. COUNTA simply looks at a range of cells and tells you how many of the cells contain data. In other words, it looks for nonblank cells. This can be useful in a variety of situations. If you've never used Excel functions, check out the Functions lesson in our Excel Formulas tutorial. COUNTA works the same in all versions of Excel, as well as other spreadsheet applications like Google Sheets. You can download our example if you'd like to work along with this article. Our exampleIn our example, we're using Excel to plan an event. We've sent out invitations to everyone, and once we receive their responses, we'll type either Yes or No in column C. As you can see, column C still has some empty cells because we haven't heard back from everyone.  Counting responsesWe're going to use COUNTA to see how many people have responded. In cell F2, type an equals sign (=), followed by COUNTA: =COUNTA As with any function, the arguments will need to go in parentheses. In this case, we only need one argument: the range of cells we want COUNTA to look at. Our responses are in cells C2:C86, but we can actually include a few extra rows in case we decide to invite more people: =COUNTA(C2:C100) When you press Enter, you'll see that we've received 55 responses. Here's the best part: We can continue to update this spreadsheet as we receive responses, and our function will automatically recalculate to give us the correct answer. Try entering Yes or No into some of the empty cells in column C, and you should see the value in cell F2 change. Counting inviteesWe can also use COUNTA to calculate the total number of people that were invited. In cell F3, type the following function and press Enter: =COUNTA(A2:A100) See how easy it is? All we had to do was type a different range (A2:A100), and it counts all of the first names, giving us an answer of 85. If you type more names at the bottom of the spreadsheet, Excel will automatically recalculate this value. However, if you type anything below row 100, you'll need to update your ranges so any new people are included. Bonus question!We now have the number of responses in cell F2 and the total number of invitees in cell F3. It would be great to calculate the percentage of people who have responded. See if you can write a formula in cell F4 that calculates the percentage. Remember to use cell references; we want the formula to recalculate whenever we update the spreadsheet. /en/excel-tips/concatenate-excels-duct-tape/content/ Using the COUNTIFS function you can achieve the result of counting numbers of cells which contain values between two numbers in a range.
Using the COUNTIFS Function Between Two ValuesThe COUNTIFS function can show the count of cells that meet your criteria. You can use COUNTIF with criteria using dates, numbers, text and other conditions. Using COUNTIFS you also need to use logical operators The COUNTIFS function is built to count cells that meet multiple criteria. In this case, because we supply the same range for two criteria, each cell in the range must meet both criteria in order to be counted. Generic formula :
Use Use So if we want to count based on criteria : Between 80 and 90 in our table, we use this formula :
Using COUNTIFS between datesThe following COUNTIFS formula shows this idea by counting the number of dates that fall between a start and end date. The following formula counts the number of dates in A2:A9 that are equal to or greater than the date in E1 and equal to or less than the date in E2: To count the number of cells that contain dates between two dates, you can use the COUNTIFS function:
So dates between 11/07/14 and 11/11/14 are found 5 dates, from B2 cell to B7 cell. The COUNTIFS formula with multiple criteriaSuppose you have a product list like in the example below, and you want to get a count of items that are in stock (value in column B is greater than 0) but have not been sold yet (value in column C is equal to 0). The task can be accomplished by using this formula:
And the count is 2 (“Cherries” and “Lemons”) When you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually. For example, here’s the right formula to count items that have 0 both in column B and column C:
This COUNTIFS formula returns 1 because only “Grapes” have “0” value in both columns. Different problems have their own best solution. If you are stuck and want to get to the solution quick, ask your question to have it answered by an Excel expert in 20 minutes. They are available to help you 24/7 at the link to the right. The first question is free. What function is used to determine the number of cells containing data in a given range select one?COUNT: To count cells that contain numbers. COUNTBLANK: To count cells that are blank. COUNTIF: To count cells that meets a specified criteria. Tip: To enter more than one criterion, use the COUNTIFS function instead.
What does Counta () function do?The COUNTA function counts cells containing any type of information, including error values and empty text (""). For example, if the range contains a formula that returns an empty string, the COUNTA function counts that value. The COUNTA function does not count empty cells.
Which function is used to count the number of cells in the range that meets any given condition?The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
How can you establish the number of cells that contain numerical data?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], …)
|
