Quickly Count Cases in Excel Using =subtotal()

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.

Move From/To to the Side of an Operator in LibreOffice

Like any decent office, LibreOffice (OpenOffice, etc.) has a formula editor to edit mathematical formulae. I wanted to have the from and to arguments to the side of the operator, but this does not seem to be foreseen. LibreOffice wants to place them at the bottom and top respectively, but if using a formula inside the main text, this really doesn’t look good. That’s the top of the illustration here.


Here’s how I worked around this limitation: I gave up on the from and to arguments, and used subscripts and superscripts instead.

Here is the code:
R = 1 - size*0.6 {{1} over {2}} sum from{i=1} to{n} lline Z_i - R_i rline
R = 1 - size*0.6 {{1} over {2}} sum^{n}_{i=1} lline Z_i - R_i rline

I also set a smaller size for the 1/2 division. Obviously this applies to any operator, sums, products, etc.