count unique items in ever-growing list?

[MeatLightning]

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!D21413<>"")/COUNTIF(Data!D21413,Data!D21413&""))]
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

 

[Barb Reinhardt]

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]

bingo! works great! Thanks so much!

The only slight hang-up is that the named range stops at the first blank (so
I just need to make sure every song in my collection has an "Album" value in
it's metadata - not a big deal really).

For those playing along at home, here are my formulas:

Sheet info:
- Album column (the target) is "D" on sheet "Data"
- Album count appears on sheet "Summary"

Named range formula:
- =OFFSET(Data!$D$1,0,0,COUNTA(Data!$D:$D),1)
- Named range is called "Album"

Album count formula:
- =(SUMPRODUCT((Album<>"")/COUNTIF(Album,Album&"")))-1
- the "-1" at the end accounts for the column header label

thanks again!
meat

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&""))]

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!D21413<>"")/COUNTIF(Data!D21413,Data!D21413&""))]
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