• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

countif referencing a cell's number to find the range?

Harry0

Harry0

Member
I know index can do it but wouldn't that being used over 1000 times with long references be safe?
What is a safe limit of how many formulas a page can use or does it depend on the power of the pc?

countif(A2:"a"&b1,1) (this does not work but would like it to work)
can I reference for example b1 which has a value of 600
It can be done with index if i say countif(a2:INDEX(a3:a1000000,b1),1) but to have 1million rows can it handle it when a formula like this is done 1000 times on the page?

In short I want to use the number in b1 for example to state how far down A should go.
is it possible to not be volatile if you do find a way?
also without vba
and can that be used over 1000 times in a page?

Thanks
 
Last edited:
Perhaps a silly question, but why do you not use a (set of) pivot(s)?
In cases like this, with many rows in the data, I used to connect pivots to the external data. Nowadays I use power query to load the data as connection only then unleash pivots/power pivot.
 
Assume B1 =1000

=COUNTIF(A2:INDEX(A3:A1000000,B1),1)

become >>

=COUNTIF(A2:A1002,1)

In order to shorten the range between "A2 to A1002"

then,

Limited the range between, "A2 to "last row value of A2:A1002""

So,

Formula will be in >>

=COUNTIF(A2:INDEX(A:A,MATCH(9.99E+307,A3:INDEX(A3:A1000000,B1))+2),1)

Regards
Bosco
 
Harry0 said:
but that would be 2x more formulas which means more processing right?
Click to expand...
1] If B1 =1000

2] All these Countif formulas will calculate 1000 times, but the dynamic range all are considered volatile include INDEX(), which will cause recalculation when workbook is opened.

=COUNTIF(A2:INDEX(A3:A1000000,B1),1)
=COUNTIF(A2:INDEX(A:A,B1+2),1)
=COUNTIF(A2:OFFSET(A3,,,B1),1)
=COUNTIF(A2:INDIRECT("A"&B1+2),1)
=COUNTIF(A2:INDIRECT("R"&B1+2&"C01",),1)

3] Only the above longer formula in Post #.5 can calculate less than 1000 times

Regards
Bosco
 
Last edited:
You must log in or register to reply here.
Back
Top