I'd do this with offset and a named range. I've only done this a couple of
times so it might not work quite right the first time.

Insert a named range with this in it:

=OFFSET(Sheet1!D2,0,0,COUNT(Sheet1!$D:$D),1)

You may need to subtract something from the COUNT piece to get it to work
properly.

Once you define the named range, I'd check it with CTRL G and enter your
range name to see if it selects the range you want.

Then enter your named range in your formulas

=SUMPRODUCT((Range<"")/COUNTIF(Range,Range&""))]

"MeatLightning" wrote:

Hey all -
I'm trying to count the number of albums I have digitized. I have a
series of columns with album info as exported from iTunes. One of the columns
lists Album Name but the album name is repeated for each record (in this case
each Song). I just want to know how many albums I have in the list.

I tried using the formula suggested in another similar post
[=SUMPRODUCT((Data!D2:D1413<"")/COUNTIF(Data!D2:D1413,Data!D2:D1413&""))]
but this requires me to continually update the max range value (i.e.
"D14138") as I import more records. Is there a formula that does the same
thing but can handle empty cells? (so I can put in something like "D9999" for
the max range value)

thanks in advance!!!
meat