How to route in the Filemaker you have defined

Method of counting values ​​in a worksheet

Counting is an integral part of data analysis, whether you're counting the number of employees in a department in your organization or the number of units sold quarter on quarter. Excel provides a number of techniques that you can use to count cells, rows, or columns of data. To help you make the best choice, this article includes a comprehensive method summary, a downloadable workbook with interactive examples, and links to related topics for more information.

Note: Counting should not be confused with totaling. For more information about totaling values ​​in cells, columns, or rows, see Totaling Methods for Adding and Counting Excel Data.

Download our examples

You can download a sample workbook that contains examples to supplement the information in this article. Most of the sections in this article refer to the appropriate worksheet within the sample workbook, which contains examples and other information.

Download examples to count values ​​in an Excel spreadsheet.xlsx

Content of this article

  • Simple counting

  • Count based on one or more conditions

  • Count when the data contains blanks

  • Count the unique occurrences of values

  • Special cases (count all cells, count words)

  • View calculations and counting results on the status bar

Simple counting

You can find the number of values ​​in a range or table using a simple formula, the click of a button, or a table function.

Excel can also show the number of selected cells in the Excel status bar. The video demonstration below briefly shows how to use the status bar. Also see the section Displaying Calculations and Counts on the Status Bar for more information. You can refer to the values ​​shown on the status bar when you want a quick look at your data and don't have time to enter formulas.

Video: Counting Cells Using the Excel Status Bar

Check out the video below to learn how to view the count on the status bar.

Using AutoSum

Use You autosum, by selecting a range of cells that contain one or more numeric values. Then click on the Formulas tab on AutoSum > Number of numbers.

Excel returns the number of numeric values ​​in the range in a cell adjacent to the selected range. Generally, this result is displayed in a cell to the right of a horizontal area or below a vertical area.

Top of page

Add a subtotal line

You can add an Excel subtotal row to your data. Click anywhere in your data and click then > Partial total.

Note: The Subtotal option only works with normal Excel and not Excel tables, PivotTables or PivotCharts.

Also read the following articles:

Top of page

Count cells in a list or Excel table column using the SUBTOTAL function

Use the PARTIAL TOTAL function to count the number of values ​​in an Excel or range of cells. If the table or range contains hidden cells, you can use SUBTOTAL to include or exclude those hidden cells. This is the main difference between the SUM and PARTIAL TOTAL functions.

The PART TOTAL syntax works as follows:

PARTIAL RESULT (function; reference1; [reference2]; ...)

If you have hidden values ​​in your range a- and want to evaluate it, you should set the function_num argument to 2 set.

If you have hidden values ​​in your range exclude want to hang up Function_num 102 .

Top of page

Count based on one or more conditions

You can count the number of cells in a range that meet conditions (also known as criteria) that you specify using a variety of table functions.

Video of using the COUNT, COUNTIF, and COUNT2 functions

See the following video for information on how to use the feature NUMBER and the functions COUNTIF and NUMBER2 to count only the cells that meet the conditions you specified.

Top of page

Counting cells in a range using the COUNT function

Use the COUNT function in a formula to find the number of numeric values ​​in a range.

In the example above, A2, A3, and A6 are the only cells that contain numeric values ​​in the range, so the output is 3.

Note: A7 is a time value, but contains text (at the.),therefore COUNT does not consider it to be a numeric value. If you at the. would remove from the cell will consider COUNT A7 as a numeric value and change the output to 4.

Top of page

Count cells in a range based on a single condition using the COUNTIF function

Use the COUNTIF function to count the number of times a specific value appears in a range of cells.

Top of page

Count cells in a column based on one or more conditions using the COUNT function

The COUNT function counts the cells that contain numbers in a field (column) of records in a list or database that meet the conditions you specify.

In the example below, you want to find the number of months, including or later than March 2016, that more than 400 units were sold. The first table in the worksheet from A1 to B7 contains the sales data.

DCOUNT uses conditions to determine which value to return the values ​​from. Conditions are usually entered in cells on the worksheet itself and then referenced in the criteria argument these Cells. In this example, cells A10 and B10 contain two conditions: one that indicates that the return value must be greater than 400, and the other that indicates that the end month should be equal to or greater than March 31, 2016.

Use the following syntax:

= NUMBER (A1: B7; "End of month"; A9: B10)

DCOUNT checks the data in the range A1 through B7, applies the conditions specified in A10 and B10, and returns 2returns the total number of rows that meet both conditions (rows 5 and 7).

Top of page

Count cells in a range based on multiple conditions using the COUNTIFS function

The COUNTIFS function differs from the COUNTIF function in only one important way: COUNTIFS lets you apply criteria to cells across ranges and counts the number of times all criteria are met. With COUNTIFS you can enter up to 127 range / criteria pairs.

COUNTIFS has the following syntax:

COUNTIFS (criteria area1; criteria1; [criteria area2; criteria2]; ...)

Take a look at the following example:

Top of page

Count based on criteria, sharing the COUNT and IF functions

For example, suppose you need to determine how many sellers have sold a given item in a given area, or you want to know how many sales above a certain value a given seller made. You can use the IF and COUNT functions together by first testing for a condition with the IF function and then, if the IF function returns True, using the COUNT function to count cells.

Hints:

  • The formulas in this example must be entered as array formulas. If you have this workbook open in Excel for Windows or Excel 2016 for Mac and want to change the formula or create a similar formula, press F2, then CTRL + SHIFT + ENTER to have the formula return the results you expected. In earlier versions of Excel for Mac, use the + SHIFT + ENTER.

  • For the example formulas to work, the second argument to the IF function must be a number.

Top of page

Count how often several text or numerical values ​​occur: use SUM and IF together

We use the functions in the following examples IF and TOTAL together. The If function first tests the values ​​in some cells, and if the test result is "True", adds up TOTAL the values ​​that pass the test.

example 1

The preceding function says: If C2: C7 the values Buhlmann and Schmitz contains, the SUM function should display the sum of the records in which the condition is met. In the specified area, the formula finds three data records for Bühlmann and one data record for Schmitz and shows 4 at.

Example 2

The above function says: If D2: D7 contains values ​​less than € 9,000 or greater than € 19,000, SUM should display the sum of all data records in which this condition is met. The formula finds two records (D3 and D5) with values ​​less than € 9,000 and two records (D4 and D6) with values ​​greater than € 19,000 and shows 4 at.

Example 3

The above function indicates if D2: D7 contains invoices for Buchanan for less than $ 9,000, then SUM should display the sum of the records in which the condition is met. The formula determines that C6 meets the condition and shows 1 at.

Important: The formulas in this example must be entered as array formulas. It means that You press F2 and then Press CTRL + SHIFT + ENTER. Use Excel for Mac in earlier versions - + SHIFT + ENTER.

For more information, see the following Knowledge Base articles:

Top of page

Count cells in a column or row in a PivotTable

A PivotTable summarizes your data and helps you analyze and drill down into your data by letting you choose the categories in which you want to display your data.

You can quickly create a PivotTable by selecting a cell in a data range or Excel spreadsheet, and then clicking the tab Insert in the group Tables on PivotTable click.

Consider a sample sales table scenario where you can count the number of golf and tennis sales values ​​for specific quarters.

Note: For an interactive experience, you can follow these steps on the sample data stored on the PivotTable sheet provided in the downloadable workbook.

  1. Enter the following data in an Excel spreadsheet.

  2. Select A2: C8.

  3. click on Insert > PivotTable.

  4. In the Create PivotTable dialog box, click Select a table or range, then on New worksheet and then on OK.

    An empty PivotTable is created in a new sheet.

  5. In the PivotTable Fields area, do the following:

    1. Drag Sports in the area Lines.

    2. Drag quarter in the area columns.

    3. Drag sales in the area values.

    4. Repeat step c.

      The field name appears in the PivotTable and Values ​​areas as Sales total2 displayed.

      At this point, the PivotTable Fields pane looks like this:

    5. Click in the area values the drop-down arrow next to Sales total2, and choose Value field settings out.

    6. Go in the dialog box Value field settings as follows:

      1. Select in the section Combine value field by the value number out.

      2. Change in the field Custom name the name in number.

      3. click on OK.

    The PivotTable displays the number of records for "Golf" and "Tennis" in "Quarter 3" and "Quarter 4" along with the sales figures.

Top of page

Count when the data contains blanks

You can use table functions to count cells that either contain data or are blank.

Count non-blank values ​​in a range using the COUNT2 function

Use the COUNT function to count only cells in a range that contain values.

Sometimes when counting cells you want to ignore all blank cells because only the cells with values ​​are important to you. For example, you would like to B. Count the total number of sellers who have made a sale (column D).

NUMBER ignores the empty values ​​in D3, D4, D8 and D11 and only counts the cells that contain values ​​in column D. The function finds six cells in column D that contain values ​​and displays 6 as Output to.

Top of page

Count non-empty cells in a list with certain conditions using the DCOUNTA function

Use the DBCOUNT2 function to count the number of non-empty cells in a column of records in a list or database that match the conditions you specified.

The following example uses the DCOUNTA function used to count the number of records in the database contained in range A1: B7 that meet the conditions specified in criteria range A9: B10. These conditions are that the "Product ID" value must be greater than or equal to 2000 and the "Ratings" value must be greater than or equal to 50.

DCOUNTA finds two lines that match the conditions - lines 2 and 4, and shows the value 2 as output.

Top of page

Counting blank cells in a contiguous range using the COUNTBLANK function

Use the COUNTBLANK function to return the number of blank cells in a contiguous range (cells are contiguous if they are all joined in an unbroken order). If a cell contains a formula that returns blank text (""), that cell is counted.

When counting cells, there are many situations where you might want to count blank cells because they mean something to you. In the following example of a table for grocery sales. Suppose you wanted to find out how many cells didn't mention the sales numbers.

Note: The COUNT BLANK worksheet function is the most convenient way to find the number of blank cells in a range to choose from, but it does not work very well if the cells of interest are in a closed workbook or if they are not a contiguous range. Knowledge Base Article XL: Using SUM (IF ()) Instead of CountBlank () shows how to use a SUM (IF ()) array formula in these cases.

Top of page

Count blank cells in a discontinuous range using a combination of the SUM and IF functions

Use a combination of the function TOTAL and the function IF. In general, you do this by using the If function in an array formula to determine whether each referenced cell contains a value, and then total the number of FALSE values ​​returned by the formula.

In this topic, you can find some examples of combinations of SUM and IF functions in an earlier section, Counting How Many Times Multiple Text or Number Values ​​Occur by Using the SUM and IF functions together in this topic.

Top of page

Count the unique occurrences of values

You can count unique values ​​in a range by using the PivotTable, COUNTIF, SUM and IF functions, or the Advanced dialog box filter use.

Counting the number of unique values ​​in a list column using "special filters"

Use the dialog box Special filters