CODEWITHSUNDEEP
excelgoogle-sheetsrowcountif
W Lee
W Lee

Reputation: 21

Count occurrences of strings just once per row in Google Sheets

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?

Upvotes: 1

Views: 658

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34230

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),"")))

Upvotes: 1

Related Questions