Estimated reading time: 12 minutes
COUNTThe function COUNT di Excel is listed in the Microsoft Excel Statistical Functions category. Returns a count of numbers from the specified values. In simple words, it only considers the values of that number and returns their count in the result.
Syntax
= COUNT(valore1, [valore2], …)
topics
valore1: a cell reference, an array, or a number entered directly into the function.[valore2]: A cell reference, array, or number entered directly into the function.Let's now see an example of function application COUNT
We used this function to count the cells of the range B1:B10 and returned 8 in the result.
In the cell B3 we have a logical value and in the cell B7 we have a text. COUNT he ignored both cells. But if you enter a logical value directly into the function, it will count it. In the following example, we have entered a logical value and a number using double quotes.
COUNTAThe function COUNTA di Excel is listed in the Microsoft Excel Statistical Functions category. Returns a count of the specified values . Unlike COUNT, considers all types of values but ignores (Cells) that are empty. In simple words, all cells are not blank.
Syntax
= COUNTA(valore1, [valore2], …)
topics
valore1: a value, a cell reference, a range of cells, or an array.[valore2]: a value, a cell reference, a range of cells, or an arrayLet's now see an example of application of the function COUNTA:
In the following example, we have used the function COUNTA to count the cells in the range B1:B11.
There are a total of 11 cells in the range and the function returns 10. There is a blank cell in the range which is ignored by the function. In the rest of the cells we have numbers, text, logical values and a symbol.
COUNTBLANKThe function COUNTBLANK of Excel is listed in the Microsoft Excel Statistical Functions category. Returns the count of empty or valueless cells. In simple words, it will not count cells that contain text, numbers or errors, but it will count formulas that return a blank value.
Syntax
= COUNTBLANK(intervallo)
topics
To test the function COUNTBLANK we need to see an example, and below is one you can try:
In the following example, we have used the function COUNTBLANK to count the empty cells in the range B2:B8.
In this range, we have a total of 3 blank cells, but the cell B7 contains a formula that results in a blank cell.
The function returned 2 since the cells B4 e B5 they are the only empty cells with no values.
COUNTIFThe function COUNTIF of Excel is listed in the Microsoft Excel Statistical Functions category. Returns a count of numbers that satisfy the specified condition. Simply put, it only considers and calculates the count of values that satisfy the condition.
Syntax
= COUNTIF(range, criteria)
topics
range: a range of cells from which you want to count the cells that meet the criteria.criteria: a criterion (case sensitive) to check for counting cells.Example
To see how the COUNTIF let's see the following example:
In the following example, we used “>2500” (as a logical operator) to count the number of customers who purchased more than €2.500,00.
If you want to use a logical operator you have to put it in double quotes.
In the example below, we used a date in the criteria to find out how many customers we have acquired since January 2022.
When you enter a date directly into the function, COUNTIF automatically converts text to a date.
In the example below, we have entered the same date as a number, and as you know, Excel stores a date as a number.
Then you can also enter a number representing a date according to Excel's date system.
COUNTIFSThe function COUNTIFS of Excel is listed in the Microsoft Excel Statistical Functions category. Returns the count of numbers that satisfy multiple specified conditions. Unlike COUNTIF, you can set multiple conditions and only count the numbers that meet all those conditions.
Innovation newsletter
Don't miss the most important news on Innovation. Sign up to receive them via email.
Syntax
= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
topics
criteria_range1: the range of cells you want to evaluate using criteria1.criteria1: the criteria you want to evaluate for criteria_range1.[criteria_range2]: the range of cells you want to evaluate using criteria1.[criteria2]: the criteria you want to evaluate for criteria_range1.To understand the function COUNTIFS we need to try it in an example and below is one you can try:
In the following example, we have used COUNTIFS to count women over 25 years of age.
We have specified two criteria for evaluation, one is “Female” and the other is a greater than operator to count cells with a number greater than “>25”.
In the following example, we used an asterisk in one criterion and the > operator in another to count the number of person whose name begins with the letter A and whose age is greater than 25 years.
FREQUENCYFor a given array of numeric values, Excel's Frequency function returns the number of values that fall within the specified ranges.
For example, if you have data on the ages of a group of children, you can use Excel's Frequency function to count how many children fall into different age ranges.
Syntax
= FREQUENCY( data_array, bins_array )
topics
Since the function Frequency returns an array of values (containing the count for each specified range), must be entered as an array formula.
Entering array formulas
To insert an array formula in Excel, you must first highlight the range of cells for the function's result. Type your function in the first cell of the range and press CTRL-SHIFT-Enter.
The array returned by the function Frequency of Excel will have one more entry than the bins_array provided. Let's look at the following examples.
Excel Frequency Function Examples
Example 1
The cells A2 - A11 of the spreadsheet contain the ages of a group of children.
Excel's Frequency function (entered into cells C2-C4 of the spreadsheet) was used to count the number of children who fall into three different age ranges, specified by bins_array (stored in cells B2 -B3 of the spreadsheet).
Please note that the values bins_array specify the maximum values for the first two age groups. Therefore, in this example, the ages should be divided into the ranges 0-4 years, 5-8 years and 9 years+.
As shown in the formula bar, the formula for the Frequency function in this example is: =FREQUENCY( A2:A11, B2:B3 )
Note that the curly braces surrounding the function indicate that it was entered as an array formula.
Example 2
The function Frequency can also be used with decimal values.
The cells A2-A11 in the spreadsheet on the right show the height (in meters) of a group of 10 children (rounded to the nearest cm).
The function Frequency (entered into cells C2-C5) is used to show the number of children whose height falls within each of the ranges: 0,0 – 1,0 meters 1,01 – 1,2 meters 1,21 – 1,4 meters and over 1,4 meters
Since we require the data to be split into 4 ranges, the function has been provided with the 3 values bins_array 1.0, 1.2 and 1.4 (stored in cells B2-B4).
As shown in the formula bar, the formula for the function Frequency is: =FREQUENCY( A2:A11, B2:B4 )
Again, the curly braces surrounding the function show that it was entered as an array formula.
For more examples of Excel's Frequency function, see Microsoft Office website .
Function error frequency
If the function frequency of Excel returns error, it is likely that this is the error #N/A. The error occurs if the array formula is entered into too large a range of cells. That is the mistake #N/A appears in all cells after the nth cell (where n is the length of bins_array + 1).
Related Readings
Una pivot table is an analytical and reporting tool used to create summary tables starting from a set of data. In practice, it allows you to synthesize, analyze e visualize data powerfully and quickly
Le pivot tables they are useful in several situations when it comes to analyzing and synthesizing large amounts of data. Here are some cases where you might want to use a pivot table:
Sales data analysis:
If you have a sales list with information such as product, sales agent, date, and amount, a PivotTable can help you get an overview of the total sales for each product or agent.
You can group data by month, quarter, or year and view totals or averages.
Summary of financial data:
If you have financial data such as income, expenses, expense categories, and time periods, a PivotTable can help you calculate total expenses for each category or view trends over time.
Human resources analysis:
If you have employee data, such as department, role, salary, and years of service, a PivotTable can help you get statistics such as average salaries by department or employee count by role.
Marketing data processing:
If you have marketing data such as ad campaigns, marketing channels, and success metrics, a pivot table can help you identify which channels are generating the greatest return on investment.
Analysis of inventory data:
If you manage a warehouse or store, a PivotTable can help you track product quantities, product categories, and sales.
In general, use a pivot table when you need to synthesize e visualize data effectively to make informed decisions
Ercole Palmeri
Developing fine motor skills through coloring prepares children for more complex skills like writing. To color…
The naval sector is a true global economic power, which has navigated towards a 150 billion market...
Last Monday, the Financial Times announced a deal with OpenAI. FT licenses its world-class journalism…
Millions of people pay for streaming services, paying monthly subscription fees. It is common opinion that you…
