Five Ways To “Count” On Excel

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. 

Happy Excelling! 

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

0 Responses to Five Ways To “Count” On Excel

  1. Ty June 28, 2013 at 10:47 am #

    The Countifs is great for sales execs like me that want to look at accounts that are up or down a certain criteria – great info!

Leave a Reply