COUNT on Formulas in Excel

Excel has a number of counting formulas that provide you great flexibility in summarizing data. There are many circumstances where it is helpful to count items in Excel. Sometimes you just want to know how many cells have “something” in them, whatever that something is. Other times, you want to know how many cells don’t have anything in them. Still other times you might want to count only cells that include specific text. Fortunately, Excel has many ways to help you count data. Let’s take a look at a few of the options available to you.

COUNT

=COUNT(range)

This formula counts the number of selected cells that contain numbers. It will ignore any cells that contain anything other than numbers for the purposes of counting. This would be helpful for counting the number of orders, for example, using the column with the final total of the order.

COUNTIF

=COUNTIF(range,criteria)

The COUNTIF function includes the cell in the count only if the contents of the cell match what you specify. An example of where I have used this function is counting replies for event attendance. I could have a spreadsheet with a column marked Attendance and then type Yes or No in the individual cells, as appropriate. Typically I would leave the fields blank to indicate someone has not responded yet (and use Conditional Formatting to turn those blank cells bright yellow, so they would stand out visually in a longer list of data). If I wanted to know the number of Yes responses in column B with a list of ten attendees, I would type the formula =COUNTIF($B$1:$B$10,”Yes”). Note that the criteria must be in double quotes. I could then copy that formula and change the Yes to No to find out how many people had declined the event invitation. I am using the dollar symbols in the formula to force Excel to only use those cells (known as absolute references) when I copy and paste the formula to a different cell.

COUNTIFS

=COUNTIFS(criteria range 1, criteria 1, criteria range 2, criteria 2,…).

The COUNTIFS function includes the cell in the count only if it matches what you specify, but the S added on the end of the function name allows you to specify more than one parameter. All criteria specified must be present in order for the cell to be counted, not just one of the criteria. An example of using this type of function would be counting the number of customers that had placed orders greater than $100,000 during the month of September. Additional criteria could be added, such as in a certain state or regional office location. You can specify up to 127 different criteria, though I confess, I can’t imagine having that many restrictions. The general format of the formula is to establish what the criteria range is and then what the criteria is. If I had 1000 orders and my Order Amount field in column D with my month of order field being in Column E, I would create the formulas as =COUNTIFS(D1:D1000,”>100000”, E1:E1000,”September”).

COUNTA

=COUNTA(range)

The COUNTA function will provide a count of selected cells that are not empty. Unlike the COUNT function, which only counted a cell if it had a number, COUNTA doesn’t care what the cell contains, as long as it is not blank. I would use this formulas to count the number of responses in a survey, for instance, where the results of each survey were typed in a separate row.

COUNTBLANK

=COUNTBLANK(range)

The COUNTBLANK function will count the number of selected cells that are empty. Using my earlier example of an event attendance list, this is a great way to know how many responses are outstanding, using the blank Attendance column cells.

I hope you enjoyed learning about these different COUNT functions that are built in to Excel. They can save you a lot of time and frustration.

About the Author:

Marie Herman CAP, OM, ACS, MOSM is the founder of MRH Enterprises LLC, whose services include teaching technology and professional development classes through corporate training and various webinars and workshops, writing articles, and more.  She leads study groups to prepare students for Google G Suite, Microsoft Office Specialist, and the Certified Administrative Professional certification exams.