Aladin, Belarius & maybe Ivan can you give it another try with FIRST EMPTY CELL posted bellow?

Info

Vera --

Care to post a sample of your data covering 2 CD's, along with expected results? Maybe you don't need to determine the first empty cell at all to achieve what you want.

Aladin

=======

Re: Info

Hi Vera

You could also use a dynamic named range to
get your last cell address....not 100% sure
of all your requirements BUT.....

goto Insert | Name | Define

In the names in workbook type in a discriptive name
eg. LastAddress
In the refers to type in Aladins formula BUT with
a twist-
=ADDRESS(MATCH(TRUE,LEN(Sheet1!$B$10:$B$35)=0,0)+8,2)

Click Add then OK

Now in any cell just type in =LastAddress
This should take care of your concerns about
NOT using array formulas.

HTH

Ivan

Re: Info

Hi Ivan,

Would that twist +9 instead of +8? It's indeed another twist that Refers to simply accepts and evaluates an array formula. By the way, I had the impression that Vera needs to determine for each CD (yes: CD) such a range in order to sum the relevant data.

Aladin

==========

Re: Info

EACH CD !.....in that case I'll reverse that and look for another option....
I'd be more inclined to use VB...BUT...thats just me.....I'm sure theres a formula NO array...
ley have another look....probably pay to get more
info as you suggest.

Ivan

Here is my new SAMPLE for 1st emtpy cell formula

I will try to replicate what I came up with last night (I am not at home where I have the file), hopefully I will not mess up again. My example will be from row 72. The time cells are formatted as [mm]:ss and the time is entered as 12:mm:ss

A B C D E

headers on row 2

CD # GROUP ALBUM SONG TIME
row72 19:19

this is the formula in E 72
{=SUBTOTAL(9,E73:INDIRECT("e"&ROW(E72)+MATCH(TRUE,LEN(E73:E89)=0,0)))}

row73 1 Deep P Fireball song a 04:47
row74 1 Deep P Fireball b 03:20
row75 1 Deep P Fireball c 03:20
row76 1 Deep P Fireball d 04:32
row77 1 Deep P Fireball e 03:20
row78 is empty
row79 08:07

this is the formula in E 79
{=SUBTOTAL(9,E80:INDIRECT("e"&ROW(E79)+MATCH(TRUE,LEN(E80:E99)=0,0)))}

row80 2 Deep P Burn song a 04:47
row81 2 Deep P Burn b 03:20

The formula (array) always adjust itself, changing the range down, depending how many songs are below it, down to the 1st empty cell.
Because is an array I have to copy/paste 2 times instead of once. Thank you so much for your guys help.

Formating the post sucks

colA-CD#
colB-GROUP
colC-ALBUM
colD-SONG
colE-TIME

So after row73 etc 1 or 2 is CD #, Deep P is GROUP, FIREBALL is ALBUM, a b c d e are SONGS, and time ofcourse.

Hope it makes sense

Re: Here is my new SAMPLE for 1st emtpy cell formula

Vera --

You don't need either SUBTOTAL bit nor the MATCH bit.

Just enter in E72: =SUM(OFFSET(E73,0,0,COUNTIF(A:A,A73),1))

Caveat. I assumed that a CD# occupies a single block of cells in A.

You can copy this by the method that you use in appropriate places.

Aladin

=======

2 issue unfortunately; CD # occupies more cells and I need to use Subtotal

Unfortunately I have to use Subtotal because I want to get the total time of all albums. And secondly, for sorting purposes in CD # column, I have the cd # in every cell for every album. Sorry, and thanks again.

Re: 2 issue unfortunately; CD # occupies more cells and I need to use Subtotal

Vera -- The following holds:

SUBTOTAL(9,range) = SUM(range)

What I meant by a block of cells is that a given CD#, say, 1, occupies a20:A40, and that it does not reappear again say in A500:A560. If you can confirm this, I'd suggest to give a try to SUM with OFFSET.

If the above not holds, try the following:

=SUM(OFFSET(E73,0,0,COUNTIF(C:C,C73),1))

Please don't dismiss the idea, just try them in a copy of your workbook.

Aladin

that's correct, appears just once; but still if I use SUM, that amount will

will not be added twice in my grant total?

Re: that's correct, appears just once; but still if I use SUM, that amount will

No, I don't believe so. In which cell do you compute the grand total?

Re: that's correct, appears just once; but still if I use SUM, that amount will

On top in E 3; as I now SUM adds up everything, that's why I use subtotal for every CD and for grant total which will disregard the totals for CDs and will add up only the subsequevent time for every song.

Re: that's correct, appears just once; but still if I use SUM, that amount will

OK. Now I understand what you are grand totaling.

In E72 enter: =SUBTOTAL(9,(OFFSET(E73,0,0,COUNTIF(C:C,C73),1))) [ uses album column ]

or

=SUBTOTAL(9,(OFFSET(E73,0,0,COUNTIF(A:A,A73),1))) [ uses CD# column ]

Copy the formula to appropriate places.

And, for the grand total in E3 enter:

=SUBTOTAL(9,OFFSET(E4,0,0,MATCH(9.99999999999999E+307,A:A)))

The last formula will not require editing as you add more data. It will always know where the data end.

Enjoy.

Aladin

Thank you so much, it's working