Formula range auto change

[johncena]

Please Login or Register  to view this content.

I'm using above formula & it's working. but some time I'm inserting new data rows above the formula & sometime deleting. my question is after adding or deleting rows above formula range should auto change as per currant data range.pls help me.thanks in advance....

[benishiryo]

hi johncena. consider these few options:
1. put a range much higher than you need. like:
=COUNTIF(A4:A1000,"*/*")

2. use the OFFSET formula to make it dynamic. would need to see a sample file to decide

3. convert the range to a Table by selecting the range (if your header is in A3 then A3:A106) & press CTRL + L. the range in the formula will now expand or decrease accordingly.

[martindwilson]

try
=COUNTIF(INDEX(A:A,4):INDEX(A:A,ROW()-1),"*/*")

[Pepe Le Mokko]

OFFSET being volatile, I would avoid it if possible

[johncena]

try
=COUNTIF(INDEX(A:A,4):INDEX(A:A,ROW()-1),"*/*")

thanks a lot & it's working perfectly.. i have another formula like bellow. pls give me solution for it..thanks a lot again..
=COUNTIF(C4:D137,"*/*")

[martindwilson]

=countif(index(c:c,4):index(d:d,row()-1),"*/*")

[johncena]

thanks a lot.it's work..