row reference range formula

Ian Bell
Ian Bell used Ask the Experts™
on
Hi,
I'm looking for a time saving formula where I don't need to enter all the ranges in for each sheet every time.
Example

=COUNTIF(AV14:AV6520,">0")

I would simply enter the following
A1    14     B1   6520

and all formulas for same row would reference the range for col AV

Many thanks

Ian
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try
=COUNTIF(INDIRECT("AV"&A1&":AV"&B1),">0")

Open in new window

Regards
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
Try below:
=COUNTIF(INDIRECT("AV14:AV"&COUNTA(AV:AV)),">0")

Open in new window

Top Expert 2016

Commented:
@Shums I wouldn't use your formula, we don't know what's above row 14 and if there are blank cells in the range
Ian Bellretired

Author

Commented:
Great one Rgonzo..... Thanks ........ it works a treat.
Ian
Ian Bellretired

Author

Commented:
I used a sum using same formula but didn't work.... any ideas ?
got a "VALUE" error

=SUM("AV"&A1&":AV"&A2)
Top Expert 2016

Commented:
=SUM(indirect("AV"&A1&":AV"&A2))

Open in new window

Ian Bellretired

Author

Commented:
ok thanks again........ I need the Indirect clause

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial