[FIXED] How to specify a range in a Google Sheets formula, where the end row number is a reference to another cell?

Issue

I have several google spreadsheets with different number of records (rows) - let's say

file 1: 200.000 records (rows)

file 2: 350.000 records (rows)

file 3: 246.000 records (rows)

etc.

I use a lot of formulas (20-30) that reference entire columns from file 1:

sumif(a$2:a$200000,">3") countif(b$2:b$200000, "=n") etc.

I want to reuse the already created formulas for the other files, but since the number of records there is different, I would have to replace the 200.000 with 350.000 for file 2 in 20-30 cells, with 246.000 for file 3 in 20-30 cells etc.

That would be too much work.

Is there a way to specify the end point of the range not with a constant but by pointing to a cell that contains the number of rows? e.g.

I would add in cell z1 the number of rows: 200000

The other formulas would contain something like

sumif(a$2:a$ (something that tells sheets to use as row number the number from z1) )

This way I would need to only replace the number in z1, and all formulas would be updated correctly. Any ideas?

I tried using indirect:

="a"&indirect("z1")

where z1 contains 200000

This pastes

a200000

But if I try using it in a range, it's not recognized as a range

=sum(a1:"a"&indirect("z1"))

Any ideas how to do that correctly?

Solution

why not just skip it... instead of:

=sumif(a$2:a$200000,">3")

use:

=sumif(a$2:a,">3")

to answer your indirecting, the correct syntax would be:

=sum(INDIRECT("a1:a"&z1))

Answered By - player0

Answer Checked By - Willingham (PHPFixing Volunteer)