3

I have a set of data in one Sheet tab, and a counter on another tab in the same document.

I want the counter (in sheet 2) to read through the text in the table in sheet 1, and update how many times one name has occurred, counting only one per line. How would I do this?

I want it to end looking like in sample spreadsheet. Same example here: input data

name1   name2   name3   name4
name3   name5   name2   name2
name1   name2   name3   name3
name4   name1   name4   name2
name5   name3   name5   name1
name2   name4   name1   name5

Output (desired result) is:

name1   name2   name3   name4   name5
5       5       4       3       3
2

The following formula counts the rows of Data sheet which contain the string given in cell A1 of current sheet:

=countunique(arrayformula(if(Data!$C2:$F = A1, row(Data!$C2:$F), )))

Explanation: the arrayformula replaces each matching string with the row number, and leaves other cells blank. Then countunique counts the number of distinct entries within this array, which is exactly the number of rows.

  • This is mostly working, but it seems to be not counting one. Searching with Ctrl+F says there's 109, and only one of those 109 is on another row, and the rest is in the range specified, but it's returning 107. Any reason for this? – JarheadHME Jun 9 '16 at 15:54
  • Does the range include all data? (The range in my example begins with row 2). Also, removing countunique will return an array where you can see what exactly is being counted. – user79865 Jun 9 '16 at 23:05
  • Yeah, it does. I looked at the array itself, it didn't look like anything wrong should be happening. Here's the sheet that I'm working with that's being miscounted: docs.google.com/spreadsheets/d/… – JarheadHME Jun 10 '16 at 4:10
  • You didn't type the formula correctly. There is a comma after row(...) part, it's there on purpose: the third argument of if is blank. As you typed it, if returns "False" when no match, and that gets counted. – user79865 Jun 12 '16 at 10:39
  • 1
    That's because your Data sheet is messy, sometimes it has space after the name, like Kyle in row 64. The strings "Kyle " and "Kyle" are considered different. To get rid of spaces, change the formula to if(trim(Data!D2:H) = ... – user79865 Jun 12 '16 at 23:02
1

If somebody likes monster-formulas, here's single formula solution:

=TRANSPOSE(
  QUERY(
    UNIQUE(
      ArrayFormula(
        {
         TRANSPOSE(SPLIT(CONCATENATE("\"&FILTER(Data!C2:F,Data!C2:C<>"")),"\")),
         TRANSPOSE(SPLIT(CONCATENATE("\"&row(offset(Data!C2,,,counta(Data!C2:C)))*COLUMN(Data!C2:F2)^0),"\"))
        }
      )
    ),
  "select Col1, count(Col2) group by Col1")
)

Explanation

The formula grabs a unique list of names and row numbers, and makes query to count them up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.