software

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!D21413lt;gt;quot;quot;)/COUNTIF(Data!D21413,Data!D21413amp;quot;quot;))]
but this requires me to continually update the max range value (i.e.
quot;D14138quot;) 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 quot;D9999quot; for
the max range value)

thanks in advance!!!
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((Rangelt;gt;quot;quot;)/COUNTIF(Range,Rangeamp;quot;quot;))]

quot;MeatLightningquot; wrote:

gt; Hey all -
gt; I'm trying to count the number of albums I have digitized. I have a
gt; series of columns with album info as exported from iTunes. One of the columns
gt; lists Album Name but the album name is repeated for each record (in this case
gt; each Song). I just want to know how many albums I have in the list.
gt;
gt; I tried using the formula suggested in another similar post
gt; [=SUMPRODUCT((Data!D21413lt;gt;quot;quot;)/COUNTIF(Data!D21413,Data!D21413amp;quot;quot;))]
gt; but this requires me to continually update the max range value (i.e.
gt; quot;D14138quot;) as I import more records. Is there a formula that does the same
gt; thing but can handle empty cells? (so I can put in something like quot;D9999quot; for
gt; the max range value)
gt;
gt; thanks in advance!!!
gt; meat

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 quot;Albumquot; 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 quot;Dquot; on sheet quot;Dataquot;
- Album count appears on sheet quot;Summaryquot;

Named range formula:
- =OFFSET(Data!$D$1,0,0,COUNTA(Data!$D:$D),1)
- Named range is called quot;Albumquot;

Album count formula:
- =(SUMPRODUCT((Albumlt;gt;quot;quot;)/COUNTIF(Album,Albumamp;quot;quot;)))-1
- the quot;-1quot; at the end accounts for the column header label

thanks again!
meat

quot;Barb Reinhardtquot; wrote:

gt; I'd do this with offset and a named range. I've only done this a couple of
gt; times so it might not work quite right the first time.
gt;
gt; Insert a named range with this in it:
gt;
gt; =OFFSET(Sheet1!D2,0,0,COUNT(Sheet1!$D:$D),1)
gt;
gt; You may need to subtract something from the COUNT piece to get it to work
gt; properly.
gt;
gt; Once you define the named range, I'd check it with CTRL G and enter your
gt; range name to see if it selects the range you want.
gt;
gt; Then enter your named range in your formulas
gt;
gt; =SUMPRODUCT((Rangelt;gt;quot;quot;)/COUNTIF(Range,Rangeamp;quot;quot;))]
gt;
gt; quot;MeatLightningquot; wrote:
gt;
gt; gt; Hey all -
gt; gt; I'm trying to count the number of albums I have digitized. I have a
gt; gt; series of columns with album info as exported from iTunes. One of the columns
gt; gt; lists Album Name but the album name is repeated for each record (in this case
gt; gt; each Song). I just want to know how many albums I have in the list.
gt; gt;
gt; gt; I tried using the formula suggested in another similar post
gt; gt; [=SUMPRODUCT((Data!D21413lt;gt;quot;quot;)/COUNTIF(Data!D21413,Data!D21413amp;quot;quot;))]
gt; gt; but this requires me to continually update the max range value (i.e.
gt; gt; quot;D14138quot;) as I import more records. Is there a formula that does the same
gt; gt; thing but can handle empty cells? (so I can put in something like quot;D9999quot; for
gt; gt; the max range value)
gt; gt;
gt; gt; thanks in advance!!!
gt; gt; meat