filter down spreadsheet formulas

Most days, as I load the dishwasher, I see the soggy, sulking coffee filter still in the sink. It’s too hot to trash first thing in the morning, and we’re too preoccupied to deal with it during the day. Sometimes, I splash dish water into it just to see the filter and grounds do their job. There’s something meditative about this encounter. Even, the airlift maneuver when I toss the mess, aiming for minimal drips. I think this belongs in the Museum of Every Day Things. “This is the everyday we spoke of” – Marie Howe echoes in my mind. Mundane? Yes. So are most of my spreadsheet maneuvers, but I find meaning in them all the same. And like the coffee filter, the filter functions that I’m writing about today help us get to the very essence of our data, without compromising quality 🙂 Hopefully, these tips will be as energizing as a cup of Joe!

Over the past few weeks, I’ve been working on a special spreadsheet challenge in support of an emergency homeless shelter. Through this project, I believe I’ve learned at least as much as I’ve contributed! *Pinches self* *Yes, this is real!* The main goal is to translate a years’ worth of client data into a series of reports to submit to HUD. Let me just say… easier said than done! I had to learn brand new spreadsheet formulas in order to achieve the results!

Typically, if I need to answer a data question like “how many events did Person X attend?” I would use a tool called a Pivot Table (they exist in both Excel and Google Sheets, although the formatting is slightly different). I would make the rows a list of people (gotta make sure that each name is unique, or else both “Roberts” will show up in the same row!) and for the Column, I would either have one column per event or one column that grouped all of the events. Each row would then represent the whether or not Robert attended the event(s) and we can easily sum them up. Works like magic! Seriously. Like maaaaagic!

But it doesn’t work super hot if you need to splice and dice data 40+ different ways, and you need them to spit out immediately or use the results in other calculations. There are lots of reasons why you might prefer a formula over a pivot table – these are just a few off the top of my head. Do you have others? Leave a comment! This is the predicament I was facing, plus a few layers of complexity on top of that.

Luckily, I reached out to my trusty spreadsheet soulmate Emily and we came up with a really elegant formulation. In this post, we’ll break the formula into its composite parts and put them back together. Then, I’ll share some more advanced filtering functions that I learned in preparation for this post!

=Rows(Unique(Filter(Names, Age >= 18, FavoriteColor = “Yellow”)))

The heading above is the formula that I used to solve this problem – and you might need to use a similar formula sometime to solve a problem of your own! Let’s explore what the different parts mean, from the outside –> in.

Rows()

The “Rows” function’s job is to count the number of rows. That’s it!

Unique()

The “Unique” function’s job is to make sure we aren’t double counting. We should only count each client once, even if they visited the shelter more than once! So we need to look at the list of names (in this case, Unique IDs), and remove the duplicate ones for the purpose of calculations. The column that comes immediately after the Unique Open Parenthesis is the data that is being filtered/uniqe-a-fied. In this case, it’s a list of Names. In your case, it can be anything, as long as it’s a Column or Range of data. More on determining your “range” later in this post! In this example, I just used the column where Names were stored.

Filter()

You thought you were done? No-sir-ee! We need to add special conditions to the formula! I included two examples. One of them shows a “greater than or equal to” comparison for ages, dates, or numbers. The second one shows a “text” comparison, which you could use for Race, Household Type, or any other data with letters/letters+numbers combined.

=Rows(Unique(Filter(Names, Age >= 18, FavoriteColor = “Yellow”)))

Ok, let’s look at this formula again, this time from the inside –> out.

• What are we counting? When all is said and done, we are counting NAMES!
• Which names are we counting? Only names connected with people who are 18 years old or older AND who’s favorite color is yellow.
• How are we counting them? First, we filter the names based on the criteria. Then, we remove duplicates. Then, we count the rows that are remaining.
• What format should the result be in? A number!

How to check your work x3

The first was, I created a super simple version of my data in a new spreadsheet … with only 3-5 names, ages, and colors. This made it much easier for me to see “with my own eyes” how many people met my criteria. Then, I would try out my formula on the data and see if I got the right answer. To make sure it wasn’t a fluke, I would update someone’s color or age and see if the formula changed. If it changed, then I new I was on the right track.

The second way was by using manual filters on the Raw Data Tab. In Google Sheets, I clicked the “funnel” button to add a little filter menu to each column in my spreadsheet. Then, I could narrow down the rows based on my selections, such as “greater than or equal to 18” in the age column, or “Yellow” in the Favorite Color column. Then, I could copy my list of names that meet that criteria, move them to a different tab, and use the “remove duplicates” button to get a number. If that number matches my formula result, I’m pretty confident that I’m on the right track.

The third way was by using a pivot table. I followed the set up steps from the beginning of the blog post, but this time I also added filter criteria to the pivot table, which you can do by clicking a few buttons. All of these methods should get you the same answer! It’s nice to know that you can use a tool that IS in your comfort zone to validate a tool that’s NOT in your comfort zone!

How to set up your data table

These steps are all good and fine, but the reality is that we won’t get to the same results if we are using different assumptions in how we organize our data table, or if our data quality is iffy. What do I mean by bad quality? One example is typed up versions of sign-in sheets. If they’re what you have, great !! we can do it!!, but you might need to spend some extra time cleaning up email addresses and looking for typos, or else your “data count” will not be very accurate.

I also find it EXTRAORDINARILY helpful to give all of my columns descriptive names, such as “FavoriteColor” instead of “Column Q1:Q200.” Named “ranges” are the gift that keep on giving. Both your future self and anyone else who comes after you will appreciate being able to look at a formula and understand what columns it references without resorting to random numbers and letters. You can learn more about this feature here (Excel) or here (Google Sheets).

Last but not least, it’s helpful to know what your rows and columns represent. In my case, my columns represented attributes about Clients and Visits to the shelter. My rows represented individual visits. So, clients who have visited more than once will have more than one row. I think it’s a really good exercise to get into the habit of answering this question, especially when it comes to interpreting/analyzing data down the line. If my rows represented Clients Only (and had nothing to do with their visit information), I would have used different types of logic to get summaries. There’s more than one right answer – this is where the creativity comes in 🙂

Hip, hip, array!

Working on these complex summary formulas gave me the opportunity to dip my toes in array formulas and whoooo-eeeee are these puppies powerful! In my limited (so far) understanding, an Array formula either uses/returns a group of cells, rather than just doing arithmetic/logic one cell at a time. For example, if you take a column of data and you type =Unique(column), you will get an entire list of values! It’s a pretty neat trick for de-duping, although I find the format kinda difficult (because you can’t type over them, since they are part of an Array).

Aaaanyway, while I was snooping to see if this =rows(unique(filter( … ))) formula combo existed on any other spreadsheet blogs (nerd alert!!!) (also… I think the answer is no??) I stumbled upon a cool way to answer a different version of my question. I wanted to document it briefly here, because I think it might come in handy later, plus I think you might enjoy stretching your brain with me.

The folks at AbleBits distinguish between “distinct” and “unique” values in a list.

• Distinct = de-duplicated list of names, where each name appears only once, regardless of how many times it appeared originally
• Unique = names that only appear ONCE in the dataset

Whereas previously, I would have done a two-step process to figure out how many times each person appears in the list, and then filter for all of the people who appear only once, this fancy shmancy formula can do both at the same time. It took me reading the explanation 5 times and trying it on my own until I was able to understand the concept. Let’s give it a try together!

SO, the “inside” of the function is taking the range A1:A9 (aka the list of names) and comparing it to… itself. That’s why A1:A9 shows up twice.

The result of that formula is: {1;2;2;1;2;2;2;1;2}. Ronnie is name number 1 and they appear in the list once. David is name #2 and he appears in the list twice. Sally is name #3 and she appears in the list twice. Jeremy is name #4 and he appears in the list once. They use the COUNTIF function because every time a name appears, it has to be counted up. Are you with me so far?

Ok, next we have an IF function (again, we’re working out way from the inside –> out). The IF function is going to look at that wonky list of 1’s and 2’s and figure out if the name appears ONCE or MORE THAN ONCE. If the name appears once (aka the value is 1), it’s going to stay a one. If the name appears more than once (aka the value is 2+), it’s going to switch to a zero. The result of this math is {1;0;0;1;0;0;0;1;0}. What this tells us is that Ronnie (Name #1) appears once in the list. David (name #2) appeared more than once in the list, so should not be included in the calculation.

Last but not least, we have the Sum function. This one should look the most familiar! It’s going to add up the results from the previous step, aka 1 + 0 + 0 + 1 + 0 + 0 + 0 + 1 + 0 = 3!

SHWOAH! My mind is totally obliterated right now!!!! It’s soo cool. And there’s even more to learn in this handy AbleBits tutorial!

Applying these techniques to social movement scenarios

Okay, journeying off into nerdland was fun, but it’s time to bring it back to social justice. (Heads up, for me, nerdland IS social justice!) Here are some examples when these filter and unique formulas might come in handy:

• Summarizing a list of volunteers based on what action skills they are/are not trained in
• Identifying a list of guests/donors who have only engaged once and you want to send them a special follow up
• Summarizing donors into “bands” or “giving levels” so that you can understand how many people gave $250 or more in a given time period
• Dividing your email list into segments based on certain criteria (like age and zip code!)
• Submitting grant reports (no really – a common scenario that lots of us face!)

I’m excited to hear from you with examples, questions, reactions, or your favorite warm beverage. In Philadelphia, we’re starting to see some colder temperatures and I think we might have a lot of time this winter to hunker down with tea and spreadsheets. As much as I miss my old life, I’m still finding ways to make meaning, and I hope the same for all of you. TTFN, TTDA!