Below in column “D” is a simple list that contains:
– 8 entries
– 2 blanks
– 4 words
– 3 numbers
– 1 number/word combination
Here are four “Count” functions in Excel and how each handles this list:
=COUNTA – this function will return the number of cells in a range that are not empty. In this case 8 of the 10 cells in the range contain some entry.
=COUNT – this function will return the number of cells containing numbers. Only 3 cells: 6, 7, & 56 contain numbers only.
=COUNTBLANK – this function will return the number of empty cells in a range. There are 2 blanks in this range.
=COUNTIF – this function will return the number of cells in a range that meet a certain condition. For this I entered the range and the condition “>6” and Excel returned the value of 2, since 2 entries were greater than 6: 7 & 56. If the criteria would have been “word” it would have returned a result of 1, since only one cell contained the EXACT match of “word” (Note: it is not case sensitive). If we wanted to get all the cells that contained “word” in some fashion, we could enter the condition *word*, with the asterisks as wild cards, and it would return a value of 2.
The last COUNT function in Excel is =COUNTIFS. It is similar to COUNTIF but allows multiple criteria. Here is a simple table of sales for certain car models:
I want to know the number of instances where “Tom” sold a “Ford”. The syntax for COUNTIFS is =COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2……). Therefore, to get the information we want, here is the formula we would enter:
Criteria range 1 is the list of salespeople (J3:J11) and “Tom” is criteria 1. Criteria range 2 is the list of models (K:3:K11) and “Ford” is criteria 2.
I hope this helps you “count” on Excel for more of your information needs.