Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Get number of rows that data takes up, including blank rows

I have a column which contains numeric values. Some rows may NOT contain a
value (ie: they're blank).

Short of filling in all the blanks with 0's, how might I be able to get the
ROW number of the final data entry?

To explain further, I am using the following formula:

=SUM(OFFSET(A1,0,0,COUNTA(A:A)))

This allows me to sum all the values in column A, using A1 as a reference
point, and making the range depth the value of the non-blank cells found by
COUNTA. Problem is, if there are blank values in between non-blank values,
the offset doesn't include those, and therefore the range depth is smaller
than the row number of the final data entry.

Hopefully, by obtaining the ROW number, I'll be able to specify that as the
offset value, as opposed to using COUNTA().

I hope I've put this clearly enough. (I've tried to keep the concept basic -
the actual formula that it will apply to is way to long and beyond the scope
of this posting)

Thanks in advance for your help.

Regards,

Denham.

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,118
Default Get number of rows that data takes up, including blank rows

Try one of these:

=SUM(A1:INDEX(A:A,MATCH(2,1/(1-ISBLANK(A1:A65535)))))
Note: that is an ARRAY FORMULA and must be committed by holding down [ctrl]
and [shift] when you press [enter]

OR
=SUM(OFFSET(A1,,,SUMPRODUCT(MAX((ROW(A1:A10000))*( A1:A10000<"")))))
Note: that formula contains OFFSET(), which is a volatile function and my
impact recalc performance in complex workbooks. It is a Non-array formula
(just press [enter])

OR
=SUM(A1:INDEX(A:A,SUMPRODUCT(MAX((ROW(A1:A10000))* (A1:A10000<"")))))
(Non-array, no volatile functions)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Denham Coote" wrote:

I have a column which contains numeric values. Some rows may NOT contain a
value (ie: they're blank).

Short of filling in all the blanks with 0's, how might I be able to get the
ROW number of the final data entry?

To explain further, I am using the following formula:

=SUM(OFFSET(A1,0,0,COUNTA(A:A)))

This allows me to sum all the values in column A, using A1 as a reference
point, and making the range depth the value of the non-blank cells found by
COUNTA. Problem is, if there are blank values in between non-blank values,
the offset doesn't include those, and therefore the range depth is smaller
than the row number of the final data entry.

Hopefully, by obtaining the ROW number, I'll be able to specify that as the
offset value, as opposed to using COUNTA().

I hope I've put this clearly enough. (I've tried to keep the concept basic -
the actual formula that it will apply to is way to long and beyond the scope
of this posting)

Thanks in advance for your help.

Regards,

Denham.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Get number of rows that data takes up, including blank rows

Hi Ron

Thanks for your feedback.

What I'm trying to avoid here is the explicit use of a defined range.

I could simply specify =sum(A1:A65535). This method, however, takes a LONG
time to recalculate as one, the formula being used is somewhat more lengthy,
and two, it's repeated about 50 times on a summary page, each time drawing
different bits of information.

In effect, I'm trying to reduce the reculculation times by rather using a
dynamic range instead of a hard coded range.

I've found a workaround by making sure that there is a column that contains
data, and making sure that it extends down as far as the data entered. (This
happens to be date column, but it makes no difference to the outcome - it's
simply a range check at the end of the day).

Still, since I'm entirely AROC (Anal Retentive Obsessive Compulsive) the
question will still bug me as to how I might accomplish this WITHOUT the use
of a check column.

In case you're very bored/interested, here's the actual formula I'm using -
it may just shed some more light.

=SUM(IF((OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$ A))=Sheet2!$C$2)*(OFFSET(Sheet1!$B$2,0,0,COUNTA(Sh eet1!$A:$A))=Sheet2!$C$3)*(OFFSET(Sheet1!$C$2,0,0, COUNTA(Sheet1!$A:$A))=Sheet2!$C$4)*(OFFSET(Sheet1! $D$2,0,0,COUNTA(Sheet1!$A:$A))=Sheet2!$C$5)*((OFFS ET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$A:$A))=B10)+(OFF SET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$A:$A))=B10)),OF FSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$A:$A))))


Once again, thank you very much for taking the time to respond to my question.

Regards,

Denham.


"Ron Coderre" wrote:

Try one of these:

=SUM(A1:INDEX(A:A,MATCH(2,1/(1-ISBLANK(A1:A65535)))))
Note: that is an ARRAY FORMULA and must be committed by holding down [ctrl]
and [shift] when you press [enter]

OR
=SUM(OFFSET(A1,,,SUMPRODUCT(MAX((ROW(A1:A10000))*( A1:A10000<"")))))
Note: that formula contains OFFSET(), which is a volatile function and my
impact recalc performance in complex workbooks. It is a Non-array formula
(just press [enter])

OR
=SUM(A1:INDEX(A:A,SUMPRODUCT(MAX((ROW(A1:A10000))* (A1:A10000<"")))))
(Non-array, no volatile functions)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get number of rows that data uses, including blank rows Denham Coote Excel Discussion (Misc queries) 5 August 22nd 06 02:10 PM
Macro question Chris Excel Worksheet Functions 12 July 7th 06 01:23 AM
Listing data without blank rows Dannycol Excel Worksheet Functions 8 May 1st 06 06:05 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
formula to use when number of rows changes dynamically confused Excel Worksheet Functions 3 August 17th 05 03:55 PM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"