offers Excel lesson videos made simple!

Sign up or log in to Magoosh Excel.

How to Count Cells in Excel

How to Count Cells in Excel -magoosh

Microsoft Excel is an electronic spreadsheet oriented Application Program devised and developed by  Microsoft, a part of Microsoft Office. It offers a wide array of features that use a grid of cells arranged in lettered columns and numbered rows to organize, formulate, manipulate, and format data. It has a plethora of supplied or user-defined functions under its wings to answer statistical, engineering, mathematical, and financial problems and fixes. In addition, it can be used to view and format data as line graphs, histograms and bar charts as well as pivot tables with a three-dimensional graphical display.

Thus, MS Excel makes the need to view, edit, organize, store, format and manipulate data easier and make the information interesting for any user. One of the ways to analyze the information is through the various functions that count cells.

Counting cells manually is usually not preferred due to the following reasons:

  1. Erroneous and strenuous nature of the task: The process of counting cells through Excel is preferred as it can be easily done on a wide array of cells, that is large in number, which otherwise, would be difficult to count manually. If the former method of counting cells manually is adopted, it may lead to error production as well as lead to waste of time due to consumption in unproductive operations.
  2. Discourages selective counting of cells based on certain conditions: When Excel is used to count cells in Excel, it facilitates selective counting of cells based on certain conditions such as counting values among duplicates, counting number of cells with textual values, counting number of cells with numeric values, and so on.

We will discuss the different methods of counting cells on the basis of conditional statements here.

Counting Number of Non-Empty Cells

This section discusses how the number of non-empty cells or basically cells, containing values and are non-void can be calculated.

The following steps need to be performed sequentially in order to count the number of non-empty cells in Excel:

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on Formulas Tab on Menu Bar, click on More Functions option under Function Library section, and further click on Statistical.
  3. Click on COUNTA function.
  4. Select the range of cells on which the built-in formula needs to be applied under Function Arguments dialog box.
  5. Click on OK.

The result or count of the number of non-empty cells would be computed.

OR

  1. Click on the cell where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNTA(Range_Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2.
  3. Press Enter Key on keyboard.

The result or count of the number of non-empty cells would be computed.

Counting Number of Empty/Blank Cells

This section discusses how the number of empty cells, or cells containing no values and are void, can be calculated.

The following steps need to be performed sequentially in order to count the number of empty/blank cells in Excel:

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on Formulas Tab on Menu Bar, click on More Functions option under Function Library section, and further click on Statistical.
  3. Click on COUNTBLANK function.
  4. Select the range of cells on which the built-in formula needs to be applied under Function Arguments dialog box.
  5. Click on OK.

The result or count of the number of empty or blank cells would be computed.

OR

  1. Click on the cell where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNTBLANK(Range_Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2
  3. Press Enter key on keyboard.

The result or count of the number of empty or blank cells would be computed.

Counting Number of Cells containing Numbers

This section discusses how the number of cells containing values which are numerical can be calculated.

The following steps need to be performed sequentially in order to count the number of cells containing numerical values in Excel:

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on Formulas Tab on Menu Bar, click on More Functions option under Function Library section, and further click on Statistical.
  3. Click on COUNT function.
  4. Select the range of cells on which the built-in formula needs to be applied under Function Arguments dialog box.
  5. Click on OK.

The result or count of the number of cells containing numerical values will be computed.

OR

  1. Click on the cell where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNT(Range_Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2.
  3. Press Enter key on keyboard.

The result or count of number of cells containing numerical values will be computed.

Counting Number of Cells containing Text

This section discusses how the number of cells containing values which are textual can be calculated. The approach follows subtracting the number of cells containing the textual value from the number of cells containing any value; that is, cells being non-void or empty. This is based on the premise that a cell is containing either textual or numerical values only.

The following steps need to be performed sequentially in order to count the number of cells containing textual values in Excel:

  1. Click on the cell in Excel where you want the result to appear.
  2. Click on Formula Bar, available under Menu Bar and type the function following the syntax:
    =COUNTA(Range_Of_Cells)-COUNT(Range _Of_Cells)
    where Range_Of_Cells is given by CellAddress1:CellAddress2.
  3. Press Enter Key on Keyboard.

The result or count of the number of cells containing textual values will be computed.

OR

The result or count of cells containing textual values can be calculated by separately calculating the result or count of cells containing numerical and non-empty/non-void values, and subtracting the former from the latter, provided that the cells contain either textual or numerical values only.

Let’s discuss two more functions in brief, which are rarely used, but can be used for conditional counting of cells.

  • COUNTIF function: Counting cells based upon a criterion.

    Use COUNTIF, one of the built-in statistical functions available in More Options option in Formula Tab, is a way to count the number of cells in Excel that meet a criterion. The count for only those cells that meet the specific or required criterion would be computed here. For example, to count the number of times a particular working hour value appears in employees list of Society Work.

    In its simplest form, the syntax of COUNTIF can be generalized to:
    =COUNTIF(Where do you want to look?, What do you want to look for?)

    COUNTIF function contains 2 arguments.

    For example:
    =COUNTIF(B2:B5,”Argentina”)
    =COUNTIF(B2:B5,B9)

  • COUNTIFS function: Counting cells based on multiple criteria.

    In its simplest form, the syntax of COUNTIFS can be generalized to:
    COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
    COUNTIFS function can thus contain multiple arguments, based upon the ranges on which the function is applied and their corresponding criteria.

    For example:
    =COUNTIFS(B2:B5,”Argentina”, B12:B15, “India”)
    =COUNTIFS(B2:B5, B9, B12:B15, B19)

In this way, the number of cells in MS Excel can be calculated.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!