This past week, I spent a good amount of time analyzing our current customers with PostageApp and figuring out some of the metrics that we use to determine the health of Postage. Of course, there’s only one tool for the job: a spreadsheet.

Being somewhat familiar with Microsoft Excel, I figured this would be a piece of cake to do on Google Docs Spreadsheets, considering I won’t be doing anything ridiculous. I’d just have to record all of our data and use a bunch of math to figure out some numbers.

Then I hit a snag: I couldn’t count the number of rows if I had more than one condition.

I was using the COUNTIF function to count the number of rows given a specific value inside a column, but it would fail if I tried to do more than one at a time.

I searched for solutions and found quite a few, but many that did not work, especially with Google Docs Spreadsheets. Finally, I stumbled onto a solution: using COUNT with FILTER!

What?

Here’s an example, multiple column data sheet that has some string information along with numeric information:

Let’s say I wanted to get a count of how many rows there were with ’223′ under the ‘Derp Column’ and ‘Herp’ under the ‘Herp Again’ column. (Don’t ask, I’ve been on Reddit far too much.) This is the formula you would use to get that count:

1

=COUNT(FILTER(B:B ; B:B=223 ; C:C="Herp"))

The important part here is the FILTER function. The first parameter (B:B)) is the row or column which you are trying to sort through (should be the one you are applying at least one condition on), which in our case is column B (‘Derp Column’), and any other parameters (as separated by the semi-colon) are conditions which you are applying to filter the row.

The result should be, of course, 1.

You can be pretty flexible when applying conditions. For example, you can also use this to figure out how many rows have a number greater than 100 in ‘Derp Column’:

1

=COUNT(FILTER(B:B ; B:B>=100))

This is a really neat way to quickly sort and count through data programmatically, and I hope it can be useful to those of you who are using Google Docs Spreadsheets to do calculations like I am!

Cheers.