By JLP | August 6, 2009
I do a lot of stuff with Excel and I have learned some things that go slightly beyond the basics and make the program that much better to use. I still use Excel 2003 so this series of posts will be based on that version.
I got the idea for this post after putting together yesterday’s post about eating out.
In the Excel spreadsheet I used for that post, I had a list of dates along with the name of the restaurant and the amount spent like this:
As you can see, the list is in chronological order. I wanted to see how many times and how much we spent (the subject of another post) at each restaurant. I could have simply used the sort feature by highlighting the data and sorted them based on the restaurant name and then counted them that way.
I could have. But, there’s a better way…
Excel has a COUNTIF function that will count items based on the parameters you provide. It’s a really cool feature. I wanted Excel to count up the number of times we ate at each restaurant so that I could arrange it in a list insert it into yesterday’s post. To use the COUNTIF function, do this:
1. Figure out where you want to put your list. I decided to put my list in column D, E, F, and G like this:
2. Create the list of restaurants that you want to count. I used the data sort feature and sorted the data by restaurant name and then made the list from there.
3. Determine and click on where you want the COUNTIF function to put your information. For my list, it was column E (cell E2), which I titled “#”.
4. Then click on Insert on the menu tab and scroll down to Function:
You should be seeing a popup like this:
Where it asks for range, you’ll need to enter the range from where your information will come from. NOTE: You’ll notice the “$” around the column letters. This fixes the range so that you can copy and paste the formula for each of the restaurants in the list.
Then, where it asks for Criteria, you simply choose the cell with the restaurant name that you want to count. In this case it is cell D2, which contains “Buffalo Wild Wings Beaumont.” Then click on “Okay.” If everything is done properly, you should see the correct count of all the cells containing “Buffalo Wild Wings Beaumont,” which is 3 in my example.
5. Simply copy cell E2 and paste it in the rest of the D column until the list is complete. It should look something like this:
Next time I’ll show you how to use the SUMIF function.
Any questions? If so, leave a comment or send me an email and I’ll see if I can answer them.