Mike wrote...
Unfortunately the data is not in a range so I can't use any A2:A7
functions.

If the data is stored in worksheet cells, then it's stored in ranges,
though likely not single area ranges.

There is a way to do this for a general, multiple area range X. It
requires using a defined name like seq referring to

=ROW(INDIRECT("1:1024"))

and it's a HUGE array formula. Like this,

=SUM(SUMIF(INDIRECT(MID(CELL("Address",(IV65536,X) ),
SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))),
SMALL(IF(MID(CELL("Address",(IV65536,X))&",",seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))))),"0"))
/SUM(COUNTIF(INDIRECT(MID(CELL("Address",(IV65536,X )),
SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))),
SMALL(IF(MID(CELL("Address",(IV65536,X))&",",seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1))))
-SMALL(IF(MID(","&CELL("Address",(IV65536,X)),seq,1 )=",",seq),
ROW(INDIRECT("1:"&(AREAS(X)+1)))))),"0"))

This assumes cell IV65536 is blank.