I was called upon this week to solve a problem using the Excel Count functions. The situation was that the user just didn’t understand the difference between each of the functions available, which I will summarise here.
There are 5 Count functions
| COUNT | Counts how many numbers are in the list of arguments |
| COUNTA | Counts how many values are in the list of arguments |
| COUNTBLANK | Counts the number of blank cells within a range |
| COUNTIF | Counts the number of cells within a range that meet the given criteria |
| COUNTIFS | Counts the number of cells within a range that meet multiple criteria |
For this example consider the following spreadsheet
Using Count to count all the numbers produces the following.
The value for the count is 23 as the count renege is A2:C13, so this includes the 12 entries for dates and the 11 entries for the monetary values.
However if using COUNTA to count all the entries
This counts all the cells in the range used (A2:C13), that have values in the cells. This function counts numbers and text values, but not the blank cell (C8).
And COUNTBLANK to count the number of cells without any entries
There being only one blank cell in the range A2:C13 this is the only cell that is counted.
Now using COUNTIF to count the cells in column C of the table that have values greater than £500 produces a total of 7. This uses a single criteria to produce the result.
Finally expanding on the above, by using COUNTIFS allows additional criteria to be added, in this case the dates were included in the criteria to extract only those entries in the table that have dates in April, and greater than £500.
Recent Comments