In a previous tip – How to create the count functions in Excel I showed you how to use the function Count and CountA. I do hope you found it both interesting and useful. Here I am continuing to explore the function as there are 5 in all and I have previously only covered two.
The five are:
COUNT, COUNTA, COUNTBLANK, COUNTIF and lastly COUNTIFS.
Countblank counts the number of empty cells in range of cells you specify.
CountIf counts the number of cells in a range that meet your condition.
Countifs counts the number of cells in a range the meet a set of conditions, i.e. more than one.
I am using a simple spreadsheet for this demonstration.
There are several cells that are blank. I am going to use the Coutblank function to calculate how many there are in this area. B2:D5.
I like to use the Insert Function tool to work on a dialog box as it is easy to see all the parts that are needed for the function.
As you can see Excel has chosen a set of cells to use which are not what we want.
As the range is highlighted in the Range box we can simply select the area we are interested in i.e. B2:D5
The answer is 3.
My scenario for this function needs to count cells that contain a value over 5000.
The answer is 7
My scenario for this function needs to count how many cells contain a value over 5000 in one specific range.
This answer is 3
Now building on that we need to know how many cells contain a value over 5000 in two ranges not just one range.
The answer is 3
When you create this function you may wish to place the different criteria on a separate sheet which allows you to view and revise the criteria. Naming these would also make your life easier and the function more understandable. See my tip on How to name cells and ranges in Excel.
Go and play with this knowledge using simple data sample that you know and understand in order to familiarise yourself with this function. Have fun!