Using the numeric contents of a cell to define row number
 

Can anyone tell me whether I can put a number in a cell and then use that
cell to define a range

e.g.

A B
1 3 CABG
2 PCI
3 CABG
4 PCI

=countif(B1:B(A1), "PCI")

The answer should equal 1

Thanks

John

Using the numeric contents of a cell to define row number
 

Try it like this:

=countif(indirect("B1:B"&A1), "PCI")

Hope this helps.

Pete

On Dec 5, 8:38 pm, JRD wrote:
Can anyone tell me whether I can put a number in a cell and then use that
cell to define a range

e.g.

A B
1 3 CABG
2 PCI
3 CABG
4 PCI

=countif(B1:B(A1), "PCI")

The answer should equal 1

Thanks

John

Using the numeric contents of a cell to define row number
 

Hi John,

=COUNTIF(B1:INDIRECT("B"&A1),"PCI")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"JRD" wrote in message ...
| Can anyone tell me whether I can put a number in a cell and then use that
| cell to define a range
|
| e.g.
|
| A B
| 1 3 CABG
| 2 PCI
| 3 CABG
| 4 PCI
|
| =countif(B1:B(A1), "PCI")
|
| The answer should equal 1
|
| Thanks
|
| John
|

Using the numeric contents of a cell to define row number
 

=COUNTIF(B1:INDEX(B:B,A1),"PCI")


"JRD" wrote:

Can anyone tell me whether I can put a number in a cell and then use that
cell to define a range

e.g.

A B
1 3 CABG
2 PCI
3 CABG
4 PCI

=countif(B1:B(A1), "PCI")

The answer should equal 1

Thanks

John

Using the numeric contents of a cell to define row number
 

Can I use this formula with sumproduct?

John

"Teethless mama" wrote:

=COUNTIF(B1:INDEX(B:B,A1),"PCI")


"JRD" wrote:

Can anyone tell me whether I can put a number in a cell and then use that
cell to define a range

e.g.

A B
1 3 CABG
2 PCI
3 CABG
4 PCI

=countif(B1:B(A1), "PCI")

The answer should equal 1

Thanks

John