How to use the Count function in MS excel

Microsoft Excel has been around for quite some time and it has greatly improved over the years. One of my favorite Excel functions is the count function. There are so many different ways to count in excel depending on what your needs are. Here are some most useful count functions in excel

Count A and Countblank

In the first example, we will use the COUNTA and COUNTBLANK function

In the example below we will use the formula to count the value on the range that we selected which is C9 up to C38. There are 30 cells in the range. Using the formula we could count the number of blank cells (countblank) and the cells that is not empty (counta)

There are five blank cells: C12,C22, C23, C24, and C25. The rest of the cells are not blank

Check out the formula indicated below

Countif and Countifs

We would be using the same data to explain how to use Countif and Countifs.

To get the number of cells with the Value of Bell in our selected range, we would need to type =COUNTIF(C9:C38,F15). This is assuming that we would type the formula on G15.

When we say range, this is where would count the number of instances the word Bell appeared. For this example, Bell is on cells C5, C11, and C23. We can use two formulas to count Bell. We can use =COUNTIF(C9:C38,F15). Let’s break down the formula. What this means count how many times Bell (F15) appears in our range (C9:C38).

There is a second option. You can use the formula =COUNTIF(C9:C38,”Bell”). The main difference is that this formula would count the value between the quotation mark. In this case, this is Bell. If we change the formula to =COUNTIF(C9:C38,”Beverly Hills”), it would give you the result of 3 because the words Beverly Hills are seen on c14, c21, and c26.

Please note that it is important the format of the value between the quotation mark should be exactly the same as the format of the value in the range. In our next example, the value on G13 is 0. That is because the value on F18 is Los Angeles-. There is an extra character at the end of the criteria that we are looking for. Los Angeles can be seen on C27 and c29. The result should be 2 if we typed Los Angeles.

Now let’s talk about Countifs.

We would use Countifs we need to match 2 or more criteria. Let me explain the formula first

=countifs(C9:C38,F19,D9:D38,F18)

=function(range1,Criteria 1, Range 2, Criteria2)

The formula would count all cells that follow two separate conditions. Look for all Bell (F23) under column C and find all Cobe (F24) in column D.

So the formula would search “Bell” under column C. There are 5 instances of “Bell” You can see them on C5, C11, C23 and C30. It would also search “Cobe” under column D. We can see that Cobe appears on D11 and D30

In this example, there are two instances where both conditions (Bell and Cobe) were met. Look at row 11 and row 30.

Conclusion

Excel might seem difficult to learn at first, but once you understand the basic function of formulas, it would be easy. Stay tuned for more Excel tutorials in the future. If you liked my Excel tutorials for more.