Shoutbox

ok here is my question.

i have a list of 2000+ music albums that i have collected over the years.

and what i want to do is have it it automatically total up all the :
rock
pop
rap
dance
soundtrack

lists that i have, but i want it to automatically keep count as i add more to each list.

how would i go about doing this?

thanks

p.s. i have searched on google and found nothing. 

I think you are looking for the function called "subtotals".
Look at the example in the subtotals tutorial on mistupid.com - Microsoft Excel Tutorials.

There is more than one way to do anything and it also depends on how your spreadsheet is organized, which you don't explain very well.

What would make sense to me (perhaps because I deal with databases a lot) would be one list, with a column for genre.  In that case, you can count the number of entries for each genre using =COUNTIFF(range, value) function on that column.

For a split list (for example, if you use different worksheet for each genre), you can use =COUNTA(range) function on any column that will be non-blank, such as title.

If neither of these fits your situation, please explain how your spreadsheet is organized.

quote:

---

Originally posted by Adeptus
There is more than one way to do anything and it also depends on how your spreadsheet is organized, which you don't explain very well.

What would make sense to me (perhaps because I deal with databases a lot) would be one list, with a column for genre.  In that case, you can count the number of entries for each genre using =COUNTIFF(range, value) function on that column.

For a split list (for example, if you use different worksheet for each genre), you can use =COUNTA(range) function on any column that will be non-blank, such as title.

If neither of these fits your situation, please explain how your spreadsheet is organized.

---

this is how my lists are setup: i have 5 lists

1) albums on various dvd's
2) burnt albums
3) bought albums
4) albums on portable harddrive
5) albums on computer

Then you need a combination of different COUNTIFs...

But:

What do you mean by 'lists'? And where are those 'lists'? Have those lists headers and how many? What is the column for the genre? etc...

For stuff like this we need very specific info.

--------

I assume by 'lists' you actually mean sheets. So each list is located on a different sheet.

I assume the genre column is located in column E on each sheet.

I assume the sheets are named:
1) DVDs
2) BURNED
3) BOUGHT
4) PORTABLE
5) PC

In the cell you want the grand total, for let's say the "rock" genre, you put:
=COUNTIF('DVDs'!E:E,"rock") + COUNTIF('BURNED'!E:E,"rock") + COUNTIF('BOUGHT'!E:E,"rock") + COUNTIF('PORTABLE'!E:E,"rock") + COUNTIF('PC'!E:E,"rock")

In the cell you want the grand total, for let's say the "pop" genre, you put:
=COUNTIF('DVDs'!E:E,"pop") + COUNTIF('BURNED'!E:E,"pop") + COUNTIF('BOUGHT'!E:E,"pop") + COUNTIF('PORTABLE'!E:E,"pop") + COUNTIF('PC'!E:E,"pop")

etc

Note: It is case insensitive

Note: If your Excel is in another language than English, refer to http://dolf.trieschnigg.nl/excel/excel.html to get the equivalent function name and syntax.

Ok.  So, how do we know what genre it is?  Is there a column for it?  Are all the lists on the same worksheet, or different worksheets?