How can I design a formula (e.g. COUNTIF) that searches multiple sheets for partial matches of text from a reference cell?

0
votes

What I want to do: Include a "Statistics" sheet at the end of my document that searches every sheet and counts the number of times that text from a reference cell (e.g., "buffers") is mentioned.

Background: I'm trying to help a student I tutor find his weak areas. For each question we go over, I have a column with 'tags' such as "buffers." Sometimes a question has multiple tags, e.g. "buffers, intermolecular forces" separated by a comma.

I've been using the COUNTIF function like this:

=COUNTIF(Sheet1!E:E,"*buffers*")+COUNTIF(Sheet2!E:E,"*buffers*")+....

This did what I needed. However, I have a lot of tags, so I would rather save time and just write Buffers in a cell in my Statistics sheet, e.g. A3, and write the formula:

=COUNTIF(Sheet1!E:E,A3)+COUNTIF(Sheet2!E:E,A3)+....

which I can then quickly copy for all my tags in separate reference cells.

When I tried this, it was only counting cells that exactly matched "Buffers," so it wasn't counting questions with multiple tags, e.g. "Buffers, intermolecular forces." This is the crux of my problem. Is there a way to format COUNTIF so it can identify cells that contain the text in the reference cell, instead of just cells that match perfectly? Similar to what I was doing with the "*buffers*"?

Follow-up: I also have a lot of sheets. What would be even better yet, if I were able to search multiple sheets in the same COUNTIF, as opposed to adding separate COUNTIFs, and count cells that only partially match the reference cell.

=COUNTIF({Sheet1!E:E,Sheet2!E:E,Sheet3!E:E},A3)

Any ideas?

2
google-sheetsgoogle-sheets-formula
Please share a google sheet with example data and I'll do it. – WARDEN
@WARDEN here is some example data. let me know if i can clarify anything. thank you so much! docs.google.com/spreadsheets/d/… – Charlie H

2 Answers

0
votes
=if(A2="","", counta(filter(Sheet1!E:E,isnumber(search(UPPER(A2),Sheet1!E:E))=TRUE))+counta(filter(Sheet2!E:E,isnumber(search(UPPER(A2),Sheet2!E:E))=TRUE)))

Don't judge I went for speed but this will do it in what you sent me. If you add more sheets just put +counta(filter(Sheet3!E:E,isnumber(search(UPPER(A2),Sheet3!E:E))=TRUE)) before the last parenthesis. This assumes the tags are in column E there as well.

You may wonder about the if statement. This should occupy every row of a row of a column of your last sheet where you get the tag totals, that way you can just can add new tags on the fly and record them at the end, if the cell is blank it will ignore it. See below:

enter image description here

Edit: for funsies here is how to get the sheet to identify all unique tags in both sheets,and tally them up automatically. So all you have to do is take the notes ...and add in the sheets to the code, but you can add a bunch at once and stockpile the empties.

Put this code into the cell that says buffers in the image above (A2):

=unique(INDEX(transpose(split(textjoin(", ",true,filter(proper(Sheet2!E:E),Sheet2!E:E<>"Tags"),filter(proper(Sheet1!E:E),Sheet1!E:E<>"Tags")),", ",false,true)),0,1))

It's ugly but it gets it done. It first gets all data in column E of both sheets that isn't "Tags", your go to column header for this, in Proper Format, so no matter if you take the note sloppy or not our deduplication will not be effected.

Similarly to above it is easy to add sheets, just put:

,filter(proper(Sheet3!E:E),Sheet3!E:E<>"Tags"))

immediately after the last "Tags")) in the equation.

It then puts all the cells into a comma separated list (the same way you separate tags in individual cells) and then once it is one long comma separated single cell string, splits it.

It then transposes (rotates 90 degrees) the data, puts it through index function in order to deduplicate it.

1
votes

You could use QUERY and use a single array to count them all:

=QUERY(
  {Sheet1!$E$2:$E;Sheet2!$E$2:$E};
  "select count(Col1) where upper(Col1) contains '"&UPPER($A2)&"' label count(Col1) ''";
  FALSE
)

Basically we get all the data (E2:E since we don't want the headers), join them into the same column and query that array.

The query is structured as follows:

  1. select count(Col1): returns the count instead of the array of values
  2. where upper(Col1) contains '"&UPPER($A2)&"': filters any row (value) that doesn't pass its test(s); in this case we are checking that it contains some value. contains is case sensitive, so I'm transforming the values and what we test against to uppercase to solve that.
  3. label count(Col1) '': sets the header for the result to an empty string. This actually makes the header disappear and only sets data to a single cell.

Result:

Table with values: Buffers: 5, Nervous System: 2, and Data Interpretation: 1

References