reference max rows in formulas

Hi,
I have a formulas on a number of sheets that I need to manually change the max row range.
I wondered if there was a way it could be automated.
Explanation on attached spreadsheet.
Thanks if you can help
Ian
Row_count.xlsx

Microsoft ExcelSpreadsheets

Last Comment

Ian Bell

8/22/2022 - Mon

Professor J

12/21/2015

you need to add this formula at the end of your formula i mean replace ROW() with the below formula. this part of formula basically finds the row number of the last non-empty cell in column A

=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))

Ian Bell

12/23/2015

ASKER

I don't follow. I was expecting to modify column "T" by modifying the range. COUNTIF($A$9:$A$24,A9)
Can you please change on sample sheet and send to me.
Many thanks
Ian

ASKER CERTIFIED SOLUTION

Professor J

12/23/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Ian Bell

12/25/2015

ASKER

Thanks Professor, It does work however it takes ages to complete over the 100k + rows
The idea of those two columns was to index cells to speed up the calc process.
I may have to just stay with the manual entry of number of rows.
Cheers
Ian

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

Certified Expert Program

Credly Partnership

Udemy Partnership