How to count x in Excel

You can watch a video tutorial here.

Excel is a great tool for analyzing and reporting data. It has several aggregation functions that can be used with text, numbers, and dates. When you want to count the number of cells that contain a particular value, you have a choice of using any one of the COUNT functions that Excel provides. In this example we will see how to count x’s for which either of 2 functions can be used:

  1. COUNTA(): this counts the number of cells that contain text in  a given range
    1. Syntax: COUNT(range of cells)
  2. COUNTIF(): this counts the number of cells that match a given value
    1. Syntax: COUNTIF(range of cells, value)
      1. range of cells: the range in which the value is to be counted
      2. value: the value to be found which may be text, number, or a date

Option 1 – Use the COUNTA() function

Step 1 – Create the formula

  • Select the cell where the result is to appear
  • Type the formula using cell references:

=COUNTA(range of Present)

  • Press Enter

Step 2 – Check the result

  • The cells that contain a value, in this case, ‘x’, are counted and the number displayed

Option 2 – Use the COUNTIF() function

Step 1 – Create the formula

  • Select the cell where the result is to appear
  • Type the formula using cell references:

=COUNTIF(range of Present, “x”)

  • Press Enter

Step 2 – Check the result

  • Since all the cells have values, the COUNTA() function counts all the cells
  • The COUNTIF() function counts only the cells that contain the value ‘x’ and the number is displayed