Excel Functions: Count, CountA, CountBlank, CountIf

One of the big challenges when working with databases is to quickly validate the data in order to use the correct numbers when calculating an average. Today, I wanted to look at a few different “count” related functions that you might find useful at one point or another depending on your need. Often, count functions are quite simply a quicker and more efficient way to get data. Imagine that you had a database with names of cities in the US that looked something like this:

I would probably use the excel function “counta” if I wanted to know the number of cities, I could simply do:

=COUNTA(B5:B54)

Why am I using CountA? Simply because this function calculates the “non blanks”. The function “Count” would actually work if I’m trying to find out how many of these cities have at least 1 pro sports team, I could do:

=COUNT(C5:C15)

Then, if I tried to do the opposite for some reason, I could use “CountBlank” which as you can imagine counts the number of cells that do not have anything entered, so I would do:

=COUNTBLANK(C5:C54)

Finally, I could also want to know how many of these cities have exactly 7 sports teams, in which case I would use Countif:

=COUNTIF(C5:C37,7)

I could use the same function to find out how many cities have more than 6 teams by simply changing the criteria:

=COUNTIF(C5:C37,”>6″)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************