referring to a cell in a formula

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I am trying to make some formulas such as, =count(A:A)
my problem is I am making several formulas for those calculations, and I run out of system resources.
I get that the better way to do this is to calculate for a range =count(A1:A5000)

Now the question.

Is there a way to refer to these cells with a value put in another cell.
for example B1 = 1, B2 = 5000
then the formula be something similar to =count(A&B1:A&B2)

Also, I don't want to change to R1C1 because I also have several macro's that use the A1 format.
 
Last edited:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=COUNT(OFFSET($A$1,B1-1,0,B2-B1+1,1)
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
Bob that is brilliant, thank you.
 
Top