Excel Functions: Count, CountA, CountBlank, CountIf

Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


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

2 Feedbacks on "Excel Functions: Count, CountA, CountBlank, CountIf"

vipul d datania

I want to know, how can i match exact figures of withdrawal by an employee with his month wise details???? Please give me a related formula to solve my this comment….

Thanks



Paresh Parekh

all exercises are good but we find the manually exercise in all functions…



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.