How to count cells that contain specific numbers in Excel

In our daily lives, the majority of activities or scenarios we happen to come across will always involve counting in one way or the other. Nearly everything depends on or revolves around counting in this world. We count to obtain the value of things numerically or in terms of numeral values.

The act of tallying a certain quantity to obtain or to reveal the number of items or several objects in a set is what we mostly refer to as counting. Counting is general and done in almost all aspects of life or our daily routines.

Counting cells that contain specific numbers in excel sheets means that the cells have certain numeric values in them. These values may either be different from the others or have one or two numbers that are making them unique.

The following are steps we follow so that we do an accurate count of cells with specific numbers. They are discussed in depth below with examples.

Step 1

Open the data you wish to work on or if you do not have any recorded data in excel sheets, open the excel sheet, and record some data into it, consider the example below.

The data set in the above excel sheet is comprised of numeric values, as you can see some numbers have a modal frequency of 34 and others a frequency of 22. Those in our case will be the specific numbers.

Step 2

Having the data to use in place in step one, we are now going to get the count of specific numbers in the cells on this step. To do the counting, we will use the COUNTIF function.

This function is used for counting the values if a certain condition is met. Our condition, in this case, is specific numbers which in our case will be 34 or 22.

We will use the COUNTIF function in the formula as follows, =COUNTIF (A2: A6, "*34*"). Write the formula in the formula bar and expect the result in the result cell B2.

In the formula above, =COUNTIF is the function and B2 and B6 are the cells while 34 is the specialty number.

Using COUNT Function to Count Cells with Numbers

1. Firstly, in your Count column highlight the output cell and type the formula. For example, assuming you have been given a range of numbers in different cells from column C, the output cell will have cells ranging from C5 to C13. Use the formula below, where the COUNT Function will count the number of cells that have numbers.

               =COUNT(C5:C14)

2. Tap the Enter button to determine the cells that have numbers in the size row.

Counting Cells with Number and Text

This method uses the COUNTA function to determine the sum of both numbers and texts in the Count column.

Step:

1. Highlight the output cell in the Count column of your cell.

2. Then, type the generic formula below;

=COUNTA(C5:C14),

Where; C5:C13 is the range between the values.

The COUNTA function will count all cells that have numbers or texts.

3. Tap the Enter button to find the numbers and texts in the Size column.

Counting Cells with Numbers in A Filtered Table

Counting cells with numbers in a filtered table gives values in the filtered column and evaluate the visible values. In this method, the SUBTOTAL function is applied.

Steps

1. Select the output cell in the Count column.

2. Type the syntax below;

              =SUBTOTAL(102,C5:C14)

Where; 102 is used in the COUNT function.

C5:C13 are the range values given in the output cell.

3. Press Enter button to find the cells with numbers before filtering in the Count column.

4. Filter the dataset as you wish, to determine the cells with numbers in the Count column which is not hidden.

Using COUNTIFS Function to Count Cells with Multiple Criteria

Basically, to determine cells that contain numbers in the Count column.

 You should base it on two ways namely;

  • Use of COUNTIF function.
  • Use of COUNTIFS function.

You will use the COUNTIFS function for values that are greater than 50.

Steps;

1. You highlight the output cell in the count column.

2. Type the formula below;

 =COUNTIFS(C5:C14,"*English*",D5:D14,"*Math*">50")

Where; B5:B14 is the first criteria range. English is the first criterion. D5:D14 is the second criteria range, and Math is the second criterion. “>50” is the second criterion.

3. Tap Enter.

4. You will see the results of the number of cells that have multiple criteria in the Count column.

Using SUMPRODUCT Function to Count Cells with Number

1. Select the output cell in the Count column.

2. Type the generic formula

=SUMPRODUCT((–ISNUMBER(C5:C13)))

where; C5:C13 is the range

The ISNUMBER function disappears. When numbers are there, they will return TRUE. If the numbers aren’t, it will return FALSE. However, it changes TRUE to 1 and FALSE to 0.

The SUMPRODUCT function will add up the totals of the values.

3. Press ENTER.

4. You will note the cells containing numbers in the Size column.

Enter COUNTBLANK Function to Count the Number of Blank Cells in a Range

COUNTBLANK function is used to count empty cells.

Steps;

1. Start by creating a new outcome cell say Cell E5.

2. You use the formula below;

=COUNTBLANK(B5:D14)

3. Press the Enter button.

4. You will note the counted number of blank cells. For example, in our case, it will be 6 with the range given B5:D14.