Using a value from a cell inside a formula!
 

Hi!

I've got two cells that contain the start and end row of a matrix in a
sheet.

I want to use these row numbers in a =COUNT.IF formula to count the
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cells
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the row
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M-


--
marsupilami
------------------------------------------------------------------------
marsupilami's Profile: http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread...hreadid=398110

try
=countif(offset(indirect("F:"&cell1),0,0,cell2-cell1+1),"argument")

"marsupilami" wrote:


Hi!

I've got two cells that contain the start and end row of a matrix in a
sheet.

I want to use these row numbers in a =COUNT.IF formula to count the
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cells
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the row
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M-


--
marsupilami
------------------------------------------------------------------------
marsupilami's Profile: http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread...hreadid=398110

=COUNTIF(INDIRECT("F"&cell1&":F"&cell2),"argument" )

--

HTH

RP
(remove nothere from the email address if mailing direct)


"marsupilami"
wrote in message
...

Hi!

I've got two cells that contain the start and end row of a matrix in a
sheet.

I want to use these row numbers in a =COUNT.IF formula to count the
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cells
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the row
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M-


--
marsupilami
------------------------------------------------------------------------
marsupilami's Profile:
http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread...hreadid=398110

marsupilami Wrote:
Hi!

I've got two cells that contain the start and end row of a matrix in a
sheet.

I want to use these row numbers in a =COUNT.IF formula to count the
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cells
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the row
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M-

Use an INDIRECT function and concatenate with the &

eg you can refer to a cell as follows

=indirect("A" & B23 & ":A" & B24)

so your formula would look something like

=countif(Indirect("F" & cell1 & ":F" & cell2, argument))


--
ronthedog
------------------------------------------------------------------------
ronthedog's Profile: http://www.excelforum.com/member.php...o&userid=26504
View this thread: http://www.excelforum.com/showthread...hreadid=398110

Another way, which I believe, is non-volatile:

=COUNTIF(INDEX(F:F,Cell1):INDEX(F:F,Cell2),argumen t)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"marsupilami"
wrote in message
...

Hi!

I've got two cells that contain the start and end row of a matrix in a
sheet.

I want to use these row numbers in a =COUNT.IF formula to count the
number of specified instances in this matrix.

But my problem is this; how can I use the values in the two cells
inside the COUNT.IF formula??

I should look something like this;

=COUNT.IF(F"cell1":F"cell2";"=argument")

As you can see, the column (F) is specified in the formula but the row
number needs to be fetched from cell1 and cell2.

Can anyone help me with the correct syntax?

Thanks!

-M-


--
marsupilami
------------------------------------------------------------------------
marsupilami's Profile:
http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread...hreadid=398110

Thanks alot guys!

-M- :)


--
marsupilami
------------------------------------------------------------------------
marsupilami's Profile: http://www.excelforum.com/member.php...fo&userid=2698
View this thread: http://www.excelforum.com/showthread...hreadid=398110