Counting Items on an Excel Spreadsheet?
Counting data entries is a topic that often puzzles users of Microsoft Excel and other spreadsheets, but it's actually not so difficult to do.
It's something we don't tend to think about when first learning to use Excel. Our first priorities are usually setting up a spreadsheet, formatting it or adding up numbers and currency in the columns. But, eventually there comes a time when we don't just want to know the total or average, we actually want to know how many times a particular value (number or text) occurs.
Here's an example of the kind of situation: if we were running a supermarket, we might want to know how many different types of fruit we usually stock. The information is probably there, on our stock list. We could use the spreadsheet filters, and then count, but there are ways to increase our efficiency. Here I will show you how to use the status bar for counts, averages and other numerical data values. I will also show you how to use the COUNTIF function to count text data items.
All the text, photos and diagrams on this page are my own. They are copyright to me, so please don't use them without my written permission and a link to this page. Thanks!
My Stock List Worksheet
I'm going to use a simple stock list spreadsheet for this exercise. My data includes twenty different food items, in five different food types:
The Status Bar in Excel
The first thing to know is how to use the Status bar - a horizontal bar at the bottom of the Excel window that gives information about the current mode and any special keys you use, such as CAPS LOCK. It allows you to zoom in and out or change between the worksheets layouts, for example from NORMAL view to PAGE BREAK preview. In addition, it has an AutoCalculate indicator that can how many cells are in a selection, the average and/or sum of all the numerical entries in a selection.
To customize the status bar, right-click on it to view the menu. As you can see there are many choices to select or de-select depending on the task in hand.
Using the Status Bar
Selecting Column D gave the following results:
From left to right, you can see that:
the average value in the cells is 9.9
the total number of items in the column is 21 (including the words "Units in Stock")
the total number of numerical items in the column is 20
the minimum units in stock is 2
the maximum units in stock is 20
the total number of items in stock is 198
That's a lot of information already!
I can use the same technique for column C to discover, for example, the average cost of an item on my stock list. For the text data in columns A and B, however, I can only get the item count of 21.
The Excel Bible
For anyone who really want to understand how to use spreadsheets in Microsoft Excel, you will find the Excel Bible very useful. Author, John Walkenbach is considered by many to be one of the top writers on using Excel.
If you are using Excel 2010, and don't intend to upgrade to Excel 2013, you will need the 2010 version of Mr Spreadsheets bible.
I can also use the Editing Group, on the Home Tab, to add up, count and find the averages of selections of number data. If I click on cell C22, to make it the active cell, then click on the Autosum button in the Editing Group, the program will enter a formula into the cell. If I agree the formula, by pressing the Enter Key, the formula will be processed - in this case it gives me the answer $56.89.
Note: this is not a particularly useful thing to do, as all I have achieved is to find the cost of "one of everything" on my stock list. It would be more useful, for example, to select Max from the Autosum drop-down menu to give me the maximum price of an item. I just give this as a reminder that you should have decided what you want to achieve before you start applying formulas hither and thither...
But, what about Counting the Text Data?
Yes, it is possible!
If I select "More Functions" from the Autosum drop-down menu, a new window will pop-up. This offers me a search box or categories to choose from, to help me find the formula I need. When you select a function from the list, an explanation appears in bold type underneath. In this case, for the function "Count". You can see that this function would only be useful for numerical data.
However, if I use the function "COUNTIF", I am not restricted to numerical data.
Agreeing to use this function (by clicking OK), brings up the "Function Arguments" window. Here I have to enter the range of cells I'm interested in. In this case the data containing cells in Column A, which is represented by A2:A21. In English, that's cells from A2 to A21 inclusive. I also have to enter the criteria I'm interested in. In this case "Dairy".
So, I have asked Excel to tell me how many times the word "Dairy" occurs in those cells. The function arguments window shows the answer of 4.
If I click OK, the active cell now shows the same result.
Making a Pie Chart in Excel
to show the Proportions of Different Food Types we Stock
Here is a little example of this function in use. First I will insert a new column to the left of food types, and enter the following data:
A23 - Dairy
A24 - Fruit
A25 - Grains/Cereals
A26 - Proteins
A27 - Vegetables
Then, I add the "CountIf" function to the following cells:
e.g. in B25, I enter
Inserting a Pie Chart
Select the cells in the rectangle A23 to B27.
Click on the "Insert Tab", and select "Pie" from the "Charts group". You can choose from a 2-D or 3-D piechart. I have chosen 3-D.
I have also selected a dramatic black background for my piechart from the "ChartStyles" on the "Design Tab".
And finally, I have labelled my piechart to show the percentages of the different types of food held in stock.
Teach Yourself Excel
© 2013 savateuse
Do You Use Excel? - Do you have any useful tips and tricks to share?
Stevie on August 18, 2015:
Found it helpful !
savateuse (author) on April 22, 2013:
@anonymous: Thanks for the feedback, it's good to know that you found this page useful!
anonymous on April 22, 2013:
Thanks for this useful information. I'm using OpenOffice, which has a countif function, too, which I wasn't aware of up until now. Makes my life easier. Thanks.
poppy mercer from London on April 13, 2013:
Thank you for such a helpful guide to Excel. It's a programme I struggle with, so this will be really useful!
savateuse (author) on April 09, 2013:
@dudokdudok: Thank you! I wrote it hoping that it would help people learning Excel.
dudokdudok on April 09, 2013:
This is really helpful. I sometimes struggle with Excel. This is a nice summary of useful things in Excel. Great lens.
savateuse (author) on April 08, 2013:
@hovirag: I puzzled over this one for a while, and eventually decided I had to learn how to use more of the Excel functions, like COUNTIF. ANd, it's actually very useful stuff to know.
VineetBhandari on April 08, 2013:
Thanks for this information.
hovirag on April 08, 2013:
This is what I still have to learn! good information
savateuse (author) on April 05, 2013:
@techmom: I know a few Excel users who don't know how to count text items. That's why I wrote this page :)
techmom on April 05, 2013:
I did not know you could count text! Thanks!
Lisa Auch from Scotland on April 01, 2013:
My husband is the excel wizard in our house, but your how to's make it easier for me to understand :)