Link to home
Start Free TrialLog in
Avatar of Ian Bell
Ian BellFlag for United Kingdom of Great Britain and Northern Ireland

asked on

modify formula for range changes

Hi,
I would like help to modify a formula to change according to number of rows in a sheet.

Cell A1
=COUNT(A8:AQ148240)

=COUNTA(V8:V4335)-COUNTIF(AV8:AV4335,"=0")

second formula to be changed to include contents of cell A1  eg range  V8:V148240  AV8:AV148240

Many thanks

Ian
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Try this. It counts the entire column and subtracts the count from the first 7 rows.

=COUNTA(V:V)-COUNTA(V1:V7)-COUNTIF(AV:AV,"=0")+COUNTIF(AV1:AV7,"=0")
The one I use for making a range is...
=OFFSET($A$1,0,0,COUNTA($A:$A),10)  10 is the number of columns for me... you can change it to what ever you like.
I did think about a dynamic range as Edward suggested, but you still have to count the entire column so why bother with that extra step?
Avatar of Ian Bell

ASKER

Could you please modify the formula I supplied so I can test
Thanks
I did. Try the formula I posted above.
Sorry I can't follow it.
I have attached a sample can you please modify to change automatically when cell A2 changes.
The cell needing modification is H3 in case of doubt.
Thanks
Ian
auto-range-change.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Wayne,
That's perfect
Thank You