How To Count A Specific Value In Excel
The Excel-FunctionCOUNT Explained:
Count Values in Cells
Written by co-founder Kasper Langmann, Microsoft Role Specialist.
Of all the things that Excel does, counting seems rather rudimentary.
But beneath the surface, there'due south then much more to it!
I guarantee you're going to be surprised by the power of Excel's COUNT function 🙂
And when you get into COUNT'due south sister functions, there are some really absurd things y'all can do.
Let's take a await at COUNT, and then get into how you lot might apply this office—and related functions—to become more valuable data from your spreadsheet.
*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a unlike version? No problem, you tin still follow the exact aforementioned steps.
The basics of COUNT
The COUNT function is rather simple. You give it a range of cells, and it tells you lot how many of those cells contain numbers.
By default, the office counts numbers, dates, and textual representations of numbers (similar "ane" and "250").
Information technology'due south a rather simple idea. And the syntax is just as straightforward:
The syntax of COUNT
=COUNT(value1, [value2]…)
The first argument, value1, is the particular or range in which you'd like to go a count. This volition almost always be a range of cells.
Boosted arguments tin exist provided to tell Excel to count the cells in multiple ranges. These are optional, but you tin include equally many every bit you desire.
Go your FREE exercise file
If you want to follow along—which we strongly recommend—you tin can download the complimentary exercise file below!
Download the Gratis Exercise File
A COUNT example
Let's have a look at an example to see how COUNT could be useful. Our example worksheet contains a brusk set of survey results.
Question #two asked respondents to charge per unit their satisfaction with a production.
Most people responded with numbers, but a few responded with text. Let'due south use COUNT to figure out how many people submitted numerical responses.
First, click into jail cell F2 and blazon "Number of numerical ratings Q2:" so we don't forget what we used this function for.
Side by side, click into G3 type "=COUNT(" to start the formula:
Then click and drag to select the values of cavalcade C. Don't include the header cell, or it volition be included in the count.
In this case, including the header prison cell won't make a difference.
But it could in future COUNTs, then we'll start getting in the addiction at present.
Kasper Langmann, Co-founder of Spreadsheeto 
Finally, close off the parentheses and hit Enter.
It doesn't become much simpler than that!
Let's get some more data from our spreadsheet. Question #iv besides asked respondents to rate their satisfaction.
How would you go near counting the number of numerical responses in both of these combined?
Again, click into a new cell; we'll use G5. Employ the same procedure as above; blazon "=COUNT(", and then select the values in column B.
Instead of closing off the formula here, though, blazon a comma and select the values in column D.
At present shut off the parentheses and hit Enter.
Yous at present have the number of numerical cells in both columns.
Counting non-empty cells
If y'all desire to count cells that contain any type of information, instead of simply numbers, you'll need COUNT's sister function, COUNTA.
This is even simpler than COUNT. It returns the number of non-empty cells in a range or multiple ranges.
If yous use COUNTA on column B in the example spreadsheet, you'll see that information technology counts everything, and gives yous a total count of fifty.
Column D, however, contains 2 blank cells and volition result in a COUNTA of 48.
Using COUNT the fast style
Because counting numerical and non-empty cells is a common chore, Excel has made information technology easy for you.
Highlight one of the columns in the spreadsheet, and look at the status bar near the bottom-right corner of the Excel window:
You'll see the count displayed in the status bar, and if you've highlighted numerical data, y'all'll likewise see the average and the sum.
The count displayed hither is the equivalent of COUNTA; information technology counts all non-empty cells.
But what if you desire to only count numerical values? Y'all can do that too.
Simply right-click on the status bar, and select Numerical Count.
Now you'll meet the numerical count (the equivalent of the COUNT office) displayed likewise.
Adding power with COUNTIF
What if you lot don't want to count everything, though?
If y'all want to count a specific subset of cells, COUNTIF will help you out. (If you're familiar with AVERAGE and AVERAGEIF, COUNTIF does the same type of matter.)
This function lets you specify a specific criterion, and it will count the number of cells that see information technology.
For example, let's look at column B, Gender. You might desire to get a count of the number of Ms and Fs in this cavalcade. COUNTIF can help.
Here's the syntax:
The syntax of COUNTIF
Equally ever, click into a cell (nosotros'll choose F9) and blazon "Number of males:" to make sure you remember what the result represents.
Kasper Langmann, Co-founder of Spreadsheeto Then, click to cell H9 and get-go the part with "=COUNTIF(".
Select cavalcade B for the range:
And, finally, blazon a comma and enter the criterion. In our case, that volition simply exist "Yard".
Close off the formula and hittingEnter.
We now have a count of the cells that comprise "Thousand". You tin can repeat with "F" to find the number of female respondents.
This could be used in many other ways as well.
If yous wanted to find out how many ratings were above five, for case, or how many people wrote "good" as their rating, COUNTIF can tell you.
Kasper Langmann, Co-founder of Spreadsheeto Counting unique values with COUNTIF
If y'all have duplicate values in your data, and you want to find out how many unique values there are, COUNTIF can help with that too. It but needs a petty help from SUMPRODUCT.
Let's start with the syntax for counting unique values. In our example workbook, nosotros'll count the number of unique values in column A to notice out how many different people took the survey.
Hither's the formula nosotros'll utilize:
=SUMPRODUCT(1/COUNTIF(range, range))
Nosotros'll start on the inside. COUNTIF, when given a range both as the range and benchmark, returns a series of values in an array. By using each value as the divisor in a fraction, and using SUMPRODUCT to combine those fractions, we become the number of unique values in a range.
Let's run this formula on the Proper name column.
Every bit you'll see, the results of COUNTA and our unique-value-counting formula are different. That difference is equal to the number of duplicates in the cavalcade.
Information technology's of import to remember that blank cells volition crusade this detail formula to throw a #DIV/0! error.
To foreclose this, use COUNTIF(range, range&" "). This will include bare cells in the count. If you lot need more flexibility with blank cells, try using the FREQUENCY office.
Wrapping things upwardly…
COUNT seems similar a rather elementary request of Excel at start, just in one case you come across the power of the counting functions, y'all'll find all sorts of uses for them.
Whether you're seeing how many items are in a listing, counting specific cases, or figuring out the number of unique values, Excel's counting functions can assistance.
And when you combine them with other functions, COUNT, COUNTA, and COUNTIF can exercise some heavy lifting! Think about where yous might put them to utilise in your own work, and you'll certainly come up with some great uses.
Source: https://spreadsheeto.com/count-function/
Posted by: gallowaycomen2001.blogspot.com
0 Response to "How To Count A Specific Value In Excel"
Post a Comment