Here’s a quick way to count the number of cases of subgroups in Excel. Let’s say you have an Excel sheet with information on survey respondents, and you wanted to count how many women and how many men there are. The first thing is to add a filter on the data. You can simply select the header and press Filter. The second thing is to add a little code to count. I usually put this on the first row, right next to the header. In that cell, type =SUBTOTAL(3,A2:A5). Subtotal is to count things (it can also to other things like averages). The 3 at the beginning is to count non-empty cells. The range (here A2:A5) indicates what to count; it should include the entire data, so if there are 10,000 cases in the data, the range would probably be A2:A10001. Press enter, and the cell shows the number of cases.
Now, obviously we didn’t need a fancy formula for just this. We could have, for example, simply have selected the first column and checked in the status bar to read Count: 5, which gives us 4 cases once we take into account the header. But we wanted to know how many women there are. Now we can use the filter, click on that small arrow next to Gender and choose only F. Once you click OK, Excel filters accordingly. The cell with our count is updated. We could have clicked on the column to check the count, but guess what, we can copy the value the formula gives: No more typos and forgetting to subtract 1 because of the header, and fewer clicks, too. We can use multiple filters, like how many women are there called Berta?
I find this useful for a quick look; for more complicated tables, a decent statistical program will be easier to use.
Didier Ruedin 