Using the numeric contents of a cell to define row number

[JRD]

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

 

[Pete_UK]

Try it like this:

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

Hope this helps.

Pete

 

[Niek Otten]

Hi John,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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
|

 

[Teethless mama]

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

 

[JRD]

Can I use this formula with sumproduct?

John

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