Counta Function

  • Thread starter Thread starter Diane White
  • Start date Start date
D

Diane White

I am working on several spreadsheets of various sizes.
All of the spreadsheets are the same type of information,
just for different businesses - so I'm using the same
formulas for each worksheet (this is a monthly report).
I would like to put in a formula that would count the
number of non-blank cells and enter the last cell (row
and column) into a formula. I used the counta function
to find the last non-blank cell, then used the
concatenate function to identify the last cell (row and
column).

I am now trying to find a way to automatically take the
identified cell and put it into a formula. Right now,
when I do calculations on the various spreadsheets, I
have the range set to calculate 25,000 rows, but some of
my spreadsheets are a lot smaller than others.

Is there a formula that will identify the last non-blank
cell and then use that cell in calculations?
 
Hi Diane
if there're no blank rows in between you may use the following for
column A:
=OFFSET($A$1,COUNTA($A:$A)-1,0)

if you have blank rows in between tyr the following depending of the
type of values in your column:
1. If you have only text values in column A try
=INDEX(A:A,MATCH(REPT(CHAR(255),255,A:A))

2. If you have only numbers in column A:
=INDEX(A:A,MATCH(9.99999999999999E300,A:A))

3. If you have both (text and values)
=INDEX(A:A,MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(25
5),255),A:A)))

3.a. or an alternative for 3.: Use the following array function
(entered with CTRL+SHIFT+ENTER)
=INDEX(A1:A10000,MAX(IF(ISBLANK(A1:A10000),0,ROW(A1:A10000))))
 
Back
Top