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:

Fig. 1

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:

Fig. 2

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:

Fig. 3

You should be seeing a popup like this:

Fig. 4

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:

Fig. 5

Next time I’ll show you how to use the SUMIF function.

### 7 responses to Excel Tidbits – Part 1: Using the COUNTIF Function

1. ::stares::

There’s … a SUMIF function?

You just made my life so much easier!

I keep track of all of my spending in my own Excel sheet. Until now, I would enter in transactions, and then go through and sum up at the bottom which belong to which account, every time.

I see a brand new world opening before me …

2. It’d be easier in Access. You can pull your transactions into an access database VERY easily, then a simple query will get you this info:
Select RestaurantName, count(*)
from RestaurantTxns
Group by RestaurantName
(you can do this in the query designer very easily without knowing sql)
You can even link an access table to a live spreadsheet, and it will refresh itself each time you run the query.

3. To create the initial list:
Data – Filter – Advanced Filter – Copy to another location – Unique records only

Best to start at the output location and work your way back to the input. You can ignore criteria for most lists. It is probably easier, but not manditory, if both the input and out columns have the same title.

4. Pivot tables make this sort of thing trivial as well, and it’s just drag-and-drop (no formulas!)

5. Thanks…I’ve always wondered how to do that. Just hadn’t discovered that function yet.

6. Props for excel posts. Thanks, JLP.

7. I agree with MichaelM on the use of Pivot Tables. It is wicked easy, and makes so much less work of sorting, configuring formulas, and coping/pasting data.