selecting range based on cell value

[greg.begin]

I want to know how can i select a range of cells and count the non blanks based on a cell value of my end range. for example. i have a value of 3 in cell A1,and i am on range b1. Based on the cell value of 3 in A1, count the nonblanks from b1 and 3 cells down?

 

[Luke M]

=COUNTA(OFFSET($B$1,,,A1))


Arguments for Offset are (reference, row_offset, column_offset, [height], [width])

In this formula, we don't need any of the offsets, we just want to specify a height, controlled by A1.

 

[greg.begin]

Your Awesome!! thanks a bunch.

 

[Luke M]

No problem, glad I could help.

=)

 

[hemasundar]

in COUNTA(OFFSET($B$1,,,A1)), please explain me what means ",,," in offset($B$1,,,A1) formula

 

[Hui]

Hemasundar


Offset has the syntax

=Offset(Reference, Row Offset, Column Offset, Row Size, Column Size)


So in the example Offset($B$1,,,A1)

,,, means

a Row Offset of Zero

and a Column Offset of Zero


so Offset($B$1,,,A1) = Offset($B$1,0,0,A1)


The result is that Counta(Offset($B$1,,,A1)) is the equivalent of saying Counta($B$1:$B$3)

 

[hemasundar]

thank you very much sir.