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
***************************************************
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…