Sheets: Count Row data Based on Key

0
votes

Im trying to find a Row based on a value, and count the number of entries (Max 5) in that row.

A B C D E F G
1 John 2 2 5 4
2 Mary 1 3 1 6 7

=COUNTA(B1:GZ) will work if I knew the row for "John",

but im trying to get the row based on a value from another cell ...

Z
10 John

In this case Z10 pseudo: =COUNTA(Find Z10 in Col A, then count entries in Row starting at B to Z)

Any ideas ? Thanks.

1
google-sheetsgoogle-sheets-formulaformulaspreadsheet
try this formula =ArrayFormula(countif(A:A&(B:Z<>""),AB2&True)) where AB2 - is the cell with the name you are looking for - Sergey

1 Answers

1
votes

Recommendation:

You can try this method

=COUNTA(QUERY(A1:Z, "Select * where A = '"&Z10&"'"))-1

Sample

Sample sheet:

enter image description here

  1. John added on cell Z10
  2. The recommended function added on cell Z11

Result: 4 was the result of COUNTA on the rows B:Z based on Column A that contains the value "John"

enter image description here

NOTE:

You may need to turn on Iterative calculation on your Spreadsheet settings.