Google-sheets – Google Sheets get row number of visible row

google sheets

I want to get the row number of the first visible row on one of my sheets. So for example if rows 1 – 499 were hidden then I want to write a formula that returns the numerical value 500.

I know this will probably require the usage of the ROW function but I'm not sure how to write the required condition.

Best Answer

Let's assume your data is in column A
Create a new column B and write =row() into every cell of that column, so it will contain the row number
Use the following formula to calculate what you need:
```
=subtotal(105,B:B)
```

It works like these: subtotal(105,range) shows you the minimum value of the range that is not hidden.

Related Solutions

Google-sheets – Get column number by referencing column title

I think this will do what you want. For example if your columns are A to D,

=iferror(match("Account Balance",A1:D1,0),"No match")

returns the column number or an error message.

Thanks for pointing out that function list. It will come in handy.

Google Sheets Query – Remove Row from Results

You can use the following formula for that:

INDEX(QUERY(Inkomster!A2:G;"SELECT SUM(C) WHERE G="&B3&" AND F='"&A3&"'"),2,0)

The 2 references the row number and the 0 or 1 references the column number

Best Answer

Related Solutions

Google-sheets – Get column number by referencing column title

Google Sheets Query – Remove Row from Results

Related Topic