Inserting rows is upsetting CountA Offset formula, don't know how to correct!

[Ironman]

Hi

Below are the earliest dates from my fitness diary that as of today's date has 7738 rows and excludes rest dates. Columns A1:G23 are shown and the date entries begin in row A12. Please note that there are formulas in some of the blank cells which are in white font as I didn't want them to be visible.

I now need to insert 889 rows for the rest dates (from Row 882 on 26 Oct 2002 the dates were all consecutive), but I've soon found that inserting the rows causes the existing CountA Offset formula in B10 that 6 other cells are dependent on to return incorrect results, and because somebody else kindly supplied the formula I don't have the knowledge to correct it.

This is the formula: COUNTA(OFFSET(A12, 0, 0, A2-36684))

I can't relate to the '36684' number or else I could probably resolve this myself.

I'd be very grateful for a solution.

Many thanks

Last saved 6:56pm, 27.9Mb	ACTIVITY	This Wk's Miles	This Wk's Time	This Wk's Pace	MLS TO NEXT Y/E TOT
3 August 2021	WK 31 EXERCISE TO DATE	15	1hr 00m	N/A	12
DAYS GONE THIS YR	WK 31 RUNNING TO DATE	0	0hr 00m		(2012 Total)
215	YEARS---------------->	2021	2020	DIFF. +/-	Lifetime/40 Years
EXERCISE DAYS LEFT	No. MILES RUN YTD---->	120	327	-207	27,648
150	No. RUNS YTD--------->	16	48	-32	5,526
Sun 01.08.21	Sun 25.07.21		Sun 18.07.21	0		02/12/1961
0 Day Exercise Streak	LONGEST DISTANCE RUN DATE IN 2021		SUN 4 JUL	14.1
2 Days Since Last Run	LONGEST DURATION RUN DATE IN 2021		SUN 4 JUL	3:41:10
	Fri 25.02.21
DATE	ROUTE	DIST	TIME	PACE	AVE HR	% MAX
Thu, 1 Jan 1998	Sutton Park	6.0	0:47:40	7:57
Sun, 4 Jan 1998	Sutton Park Full Circuit	8.2	1:15:36	9:13
Sun, 11 Jan 1998	Sutton Park	6.5	0:52:08	8:01
Tue, 20 Jan 1998	West Bridgford	7.3	0:58:14	7:59
Fri, 27 Feb 1998	Pype Hayes Park	4.0	0:38:50	9:43
Sun, 8 Mar 1998	Sutton Park Full Circuit	8.2	1:12:14	8:49
Sun, 15 Mar 1998	Sutton Park	6.3	0:50:14	7:58
Tue, 17 Mar 1998	Sutton Park	4.4	0:34:52	7:55
Sun, 22 Mar 1998	Sutton Park Full Circuit	8.2	1:01:28	7:30
Fri, 27 Mar 1998	Sutton Park	6.0	0:48:12	8:02
Sun, 29 Mar 1998	Sutton Park	4.4	0:35:22	8:02
Mon, 30 Mar 1998	Sutton Park	5.0	0:40:22	8:04

 

[steve the fish]

So its counting filled cells from A12 downwards. The part using the date and -36684 is there to increment the number of cells included by 1 daily. Currently you are offsetting A12 by 7727 cells. If you want more cells included then the 36684 needs to be smaller.

 

[Ironman]

So its counting filled cells from A12 downwards. The part using the date and -36684 is there to increment the number of cells included by 1 daily. Currently you are offsetting A12 by 7727 cells. If you want more cells included then the 36684 needs to be smaller.

Thanks! Is it as simple as reducing 36684 by 889?

 

[steve the fish]

Yes you will notice the count increases (assuming the cells are filled).

 

[Ironman]

Hey, I've just inserted 6 rows and reduced the the 36684 to 36678 and all's well!

Thank you so much for your help Steve, that means a lot to me.