Formula range auto change
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....
=COUNTIF(A4:A106,"*/*")
Last edited by johncena; 02-06-2013 at 10:08 AM.
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.
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
try
=COUNTIF(INDEX(A:A,4):INDEX(A:A,ROW()-1),"*/*")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
thanks a lot & it's working perfectly.. i have another formula like bellow. pls give me solution for it..thanks a lot again..Originally Posted by martindwilson
=COUNTIF(C4:D137,"*/*")
OFFSET being volatile, I would avoid it if possible
=countif(index(c:c,4):index(d:d,row()-1),"*/*")
thanks a lot.it's work..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks