As I have listed down all the useful excel functions relevant for data analysis in my previous post, here I will elaborate on the ‘Count’ functions with examples.
Count – This function simply gives us the count of cells containing numeric cells.Interestingly, if we have empty cell or have any text in the cells this function will ignore them.Let’s understand this with the following example –
Here we have a list of product names in the column G along with their sales and profit in the columns H and I respectively.So basically we have numbers in column H and I and texts in column G.So if we use the formula COUNT(H2:H16) it will return a count of 15.But, if we would have used COUNT(G2:G16) it would have returned 0 as column G contains only texts.
Now if we want to count the number of cells in a column or row containing only texts (similar to column G) we can simply use COUNTA(G2:G16) and it will return 15 as well.
Please note that if we simply select a particular range of a column or row in excel it will automatically show us the count (sum and average as well) as shown below.However,knowing these functions will help us to use them as part of any formula.
CountBlank -If we want to know how many empty cells we have in a particular column we can use Countblank function.In the example below we can see if we use the COUNTBLANK function for the range (B2:B6) it will return a value of 2 which are the number of empty cells.
COUNTIF – Now let’s look at the following data set.This is just an extension of the data we already had.
If we want to find the count of transactions for the month of January, we can simply use COUNTIF function as below:
COUNTIF(range,criteria)
Here the range is the column of month (excluding the label) and the criteria is the month ” January”.The formula will return a value of 8.
COUNTIFS – Now what if we want to find the number of transactions for product ‘A’ in the month January.We can use COUNTIFS function as follows.
COUNTIFS(range1,criteria1,range2,criteria2,…)
Here criteria 1 is product ‘A’ and range will be the column G (without the label)and criteria 2 is January and the range will be column H (without the label).The function will return a value of 3 as shown below.
Hope you found this post helpful. Happy learning 🙂