It’s been more than two years since I wrote a post on using Google Spreadsheets to use COUNT and FILTER in tandem to count values with multiple conditions.
Circling back, it does very well at allowing you to count when you need to validate multiple conditions. However, it falls apart the minute that your data is either messy or comes grouped with other data inside of a cell. Here’s an example where my original post would have trouble with:
Why does it fall apart? Each cell can have multiple values inside of it.
To dig a little further, we have to understand that my original post called for using the FILTER function to pare down the data set into the specific rows that we need, and then using the COUNT function to count the result of the filter. However, FILTER does not allow for wildcards in its conditions for filtering, making it extremely difficult to account for data that is in the same cell as other data.
Luckily, there’s an easy way to get around it, using a crude but workable solution for counting values with multiple conditions, with sources that have multiple values in cells, extensible to messy or not-very-nice data.
The Scenario
In the above example, let’s say we’d like to count the number of items inside of Column A and Column B for every row that Item A appears in.
Manually counting them, we know that for Item A, it is listed on a total of 3 rows (Rows 2, 3, and 7) and has 7 values listed to its name in Column A, and 4 values listed in Column B. Hopefully I haven’t lost you, but I’m saying that we count the number of values inside Column A every time Item A is listed, and we do the same for Column B.
How would we go about doing this in Google Spreadsheets?
For the impatient, here’s what the formula looks like:
=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))
I know, I know, it looks odd and confusing, so let’s break it down starting from the very inside formulas.
=SEARCH("*A*", A:A)
This is what we’re using as a condition inside of FILTER. SEARCH allows us to parse through the values in a column or row and find characters or values that we’re looking for. In this particular instance, I am looking for the character “A”, but you could be looking for whole words, sequences of characters, you name it, and you have the possibility of adding wildcards on the outside of the term you’re looking for to make it catch every instance properly. Experiment with this, because I’m not 100% sure on whether or not the wildcards help in every situation.
=FILTER(B:B, SEARCH("*A*", A:A))
When SEARCH is used in conjunction with FILTER, it returns a list of rows or columns (as specified by the first parameter in FILTER, in this case it’s B:B) that returned a TRUE condition for the SEARCH. The result would look like this:
X, Y, Z X, Y, Z Z
This is returning B2, B3, and B7 respectively from my above example, because they had Item A in their Item column.
=JOIN(",", (FILTER(B:B, SEARCH("*A*", A:A)))
Using JOIN, you can combine all of our FILTER results into a single cell, where each cell’s values are separated by a comma (first parameter from JOIN, in this case “,” means separate values using a comma) and makes it nice and simple for us to analyze just this one cell.
X, Y, Z,X, Y, Z,Z
This is what our data now looks like, all inside of a single cell.
=COUNTA(SPLIT(JOIN(",", FILTER(B:B, SEARCH("*A*", A:A))), ","))
We arrive back at the original Google Spreadsheets formula, with SPLIT and COUNTA appearing. SPLIT allows us to break apart a single cell into many cells that contain each and every value that we have in the cell, as long as we let it know how the data is separated. In this case, it’s separated by commas because of the work we did above with JOIN, and we use COUNTA to count the non-numeric values that the data represents.
Doing this for Item A, we can quickly see that it has 7 items in Column A, and 4 items in Column B.
Easy peasy eh?
I linked the functions into the documentation for Google Spreadsheets as I listed them above, but here they are again, just in case:
If you end up trying this out, let me know what you think, or if you have any questions, or if you have any improvements or suggestions!