Google Spreadsheets: Using COUNT with Multiple Conditions

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 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.

Beauty of a Bride

On July 24th, my friend Belle asked me to help her film her bridal shower, and of course I couldn’t say no to my former LIVE Conference Co-Chair! It was a really fun, cute, and sweet event, makes me really happy to see Belle surrounded by such amazing and dedicated friends and family.

Still learning to shoot with a DSLR, but we’re getting there. Slowly.

Away for the Weekend

Tomorrow morning, my friends and I depart for a weekend getaway to a cottage for the weekend. I am excited and I cannot wait to eat, drink, and be merry with fantastic company.

I am trying to minimize the amount of electronics I bring along (since I always seem to bring the entire store) and I am only bringing the following:

  1. My iPhone: Not so much for making calls or tweets, but just to be able to stay in contact if necessary and to play all of the music that I have downloaded in preparation.
  2. My Kindle: I am going to spend a lot of time sitting around and relaxing with a cool drink during the day, and what better way to relax than to read my many books?
  3. My SLR: I’m going north to beautiful scenery over a lake with friends. Do I really need to say more?

So it’s still a lot more than most people ever bring along, but I’m a digital native, sue me.

Have a great weekend, folks!

Facebook Photo Comment View is Back!

When Facebook revamped its photo gallery feature, it removed one of my favourite features – View All Commented.

In short, you could view which photos in a certain gallery were commented. That way, you can go through comments and easily respond all on one page.

Well… it’s back!

Excitement! It works exactly as it used to, so thank you Facebook gods, for making my life that much easier.

Handing off the SLR

While away on vacation, I would love nothing more than to have more pictures of myself actually enjoying the change in location and scenery. I bring a rather nice camera along with me (Canon T2i with an 18-55mm and a 55-250mm lens) that take wonderful pictures… in the right hands. I always hesitate to hand off the camera to a stranger (or even friends!) because the picture that they will take will (usually) not look the way I would have imagined it.

Is that a douchey way to think? Of course! But what do you expect? They aren’t me and they won’t know what sort of shot I am looking for.

What is a guy to do?

While on vacation in Miami, I could only think of one solution: eliminate any variables that you can control before giving up control of your camera.

Before handing off my camera to the waiter to get him to take my picture, I did several several things:

  • Switch the lens to “Manual Focus”
  • Set the right shutter speed
  • Set the right aperture
  • Set the right ISO
  • Turn on the LCD viewfinder

My trick was to make it as simple as a point and shoot camera is to use, without having to have them fiddle with any of the settings.

Once you get used to asking people to take pictures of you with your SLR, you will be able to finally have pictures of yourself in front of the camera for once!

I Came for the Post-Apocalyptic Setting, I Stayed for the Nerdiness

I finished reading The Oblivion Society by Marcus Alexander Hart on Friday. I enjoyed reading it, especially when you have gems like this:

“I’m with you,” Bobby agreed. “Let’s eat already. I feel like somebody did an ‘rm star’ on my stomach.”

It makes me sad that I read that, understood it, and laughed so hard that my fellow subway riders probably thought I was crazy.

Of course, there was this as well:

“We should all find souvenirs,” she said. “Look around. There’s probably some stuff we can use in here. At the very least we should all find something to wear to keep warm.” “Hell yeah,” Sherri said, loosening the belt on her coat. “I call dibs on the first thing anyone finds that’s black and encrusted in metal studs!” “Fine,” Bobby said. “I’ll let you know if I find Dennis Rodman.”

And don’t even get me started on all of the Canada-related puns on the billboards they kept running into. Oh boy.

Great read.

The Cake Isn’t A Lie

Yesterday, I tweeted out that I was going to give CakePHP a try over the weekend, with the end goal of creating a plugin for CakePHP to be able to send emails via PostageApp.

CakePHP!

Welp, I’ve done the bare minimum to give it a try – I went through the ‘create a blog’ example they had in their documentation.

What you end up with is a pretty simple web page that lists all of the posts (title and body) that are in your database, and you can add, edit, or delete posts as you see fit. It was pretty darn easy to set up, and it gave me a much better understanding of the MVC coding process.

I’m going to fiddle with it some more until I can actually add functionality to it (thinking of adding comments) and some sort of layout. If you want to check out what I’ve built so far, here’s the link: hellocake.upatom.com

Feel free to add/edit/delete posts on the site, and even feel more free to leave me weird messages!

Let’s see how far I can get with this!