Count occurrences of strings just once per row in Google Sheets

Question

I have strings of spreadsheet data that need counting by 'type' but not instance.

   A   B C D
 1 Lin 1 2 1
 2 Tom 1 4 2
 3 Sue 3 1 4

The correct sum of students assigned to teacher 1 is 3, not 4. That teacher 1 meets Lin in lessons B and D is irrelevant to the count.

I borrowed a formula which works in Excel but not in Google Sheets where I and others need to keep and manipulate the data.

F5=SUMPRODUCT(SIGN(COUNTIF(OFFSET(B$2:D$2, ROW($2:$4)-1, 0), E5)))

  A   B C D E 
2 Lin 1 2 1
3 Tom 1 4 2
4 Sue 3 1 4
5           1 [exact string being searched for, ie a teacher name]

I don't know what is not being understood by Google Sheets in that formula. Does anyone know the correct expression to use, or a more efficient way to get the accurate count I need, without duplicates within rows inflating the count?

Please describe what the rows and columns signify in the example you provided. — Jonas
– Jonas, Commented May 31, 2018 at 8:40
Each row is a record of teaching support to one student. Each column B, C and D may or may not contain a string which is a teacher's name. Cell B1 and D1 both contain a teacher's name such as Smith. Lin is supported by Smith in two different lessons but I need to count that Smith supports Lin, Tom and Sue = 3 students. — W Lee
– W Lee, Commented May 31, 2018 at 8:45
I would CONCATENATE the rows and use FIND and IF and ISERROR, to get 0or1 for each row - like =IF(ISERROR(FIND($A$1,CONCAT(A3:D3))),0,1) - and then probably make that an array formula. — ashleedawg
– ashleedawg, Commented May 31, 2018 at 8:49
That sumproduct is wrong. The offset should be using ROW($1:$3)-1 or ROW($2:$4)-2, not ROW($2:$4)-1. — user4039065
– user4039065, Commented May 31, 2018 at 8:52
I don't think offset will work in the way you want in Google Sheets. Mmult should work in both, or there could be a specific google sheets way probably using split/join/transpose - not of them as straightforward as you'd wish unfortunately. — Tom Sharpe
– Tom Sharpe, Commented May 31, 2018 at 9:10

Tom Sharpe · Accepted Answer · 2018-05-31 13:42:06Z

1

So this is the mmult way, which works by finding the row totals of students assigned to teacher 1 etc., then seeing how many of the totals are greater than 0.

=ArrayFormula(sum(--(mmult(n(B2:D4=E5),transpose(column(B2:D4)))>0)))

or

=ArrayFormula(sum(sign(mmult(n(B2:D4=E5),transpose(column(B2:D4))))))

Also works in Excel if entered as an array formula without the ArrayFormula wrapper.

A specific Google Sheets one can be quite short

=ArrayFormula(COUNTUNIQUE((B2:D4=E5)*row(B2:D4)))-1

counting the unique rows containing a match.

Note - I am subtracting 1 in the last formula above because I am assuming there is at least one zero (non-match) which should be ignored. This would fail in the extreme case where all students in all classes are assigned to the same teacher so you have a matrix (e.g.) of all 1's. This would be more theoretically correct:

=ArrayFormula(COUNTUNIQUE(if(B2:D4=E5,row(B2:D4),"")))

edited May 31, 2018 at 13:42

answered May 31, 2018 at 9:35

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

W Lee Over a year ago

I see the reference to the cell E5 where I have written the string I am searching for (a teacher's name). I will try this.

Collectives™ on Stack Overflow

Count occurrences of strings just once per row in Google Sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related