12 Replies Latest reply: Nov 27, 2012 11:28 AM by Jerrold Green1
Level 1
iLife

I am using Numbers '08, Mac OS X 10.8.2

I have a spreadsheet with a number of different tables. Each table refers to a style of music and lists songs in that style. I want to count the number of songs in each table (bearing in mind that the first row in each table is a heading and would not be counted). I also want this number to automatically update each time a new song is added or removed. Is there are way of adding this number count into data that is already stored or would I need to start from scratch with a specific table style?

MacBook Pro, OS X Mountain Lion (10.8.2)
Solved by Badunit on Nov 26, 2012 8:39 AM Solved

For the first table, you could use the formula =ROW()-1 to give the number for each song. Or, if you want it to have the period after it, =(ROW()-1)&"."

For the second, you need the count or the final number from the first table. Getting the final number from the previous table is easy if you don't put the period after the number, which turns it into a string and not a number.

So here is my recomendation:

Table 1 Column A has the formula =ROW()-1

Table 2 Column A has the formula = MAX(Table 1::A)+ROW()-1

Table 2 Column A = MAX(Table 2::A)+ROW()-1

Column B's of all the tables have the formula = A&"."

etc. following this pattern

Hide column A of all these tables.

Feel free to use different columns than those in this example.

#### All replies

• Level 6

I don't have '08 (so I don't know what formulas are and are not available to you) but in '09:

If the tables have no empty rows, a count of the number of rows should suffice.

=ROWS(Table 1::A)-1  assuming a single header row and no footer row

If the tables might have blank rows,

=COUNTA(Table 1::A) - 1

In this case, pick a column that will definitely be filled in whenever you add a song.

• Level 1
iLife

I should have explained myself a bit clearer as it's not just the total of each table I want. Basically want I really want is each table to be a list so that each song has a number before it.

Example

1. Song Name
2. Song Name
3. Song Name

Basically I could go and manually add in the numbers, but there are hundreds on the list and I also want them to update automatically if I add or take out a song. Every row will have a value in it so no issues there, however the header row in each table should not be counted.

Sorry for any confusion!

• Level 1
iLife

Sorry just realised that I also want the numbers to continue on from the previous table....

For example....

Table 1 - Jazz

1. Song Name

2. Song Name

3. Song Name

Table 2 - Pop

4. Song Name

5. Song Name

6. Song Name

Don't if this is possible?

Each row is a different song name and every row has a value in it. The first row of each table is the header row and therefore would not be counted.

• Level 6

For the first table, you could use the formula =ROW()-1 to give the number for each song. Or, if you want it to have the period after it, =(ROW()-1)&"."

For the second, you need the count or the final number from the first table. Getting the final number from the previous table is easy if you don't put the period after the number, which turns it into a string and not a number.

So here is my recomendation:

Table 1 Column A has the formula =ROW()-1

Table 2 Column A has the formula = MAX(Table 1::A)+ROW()-1

Table 2 Column A = MAX(Table 2::A)+ROW()-1

Column B's of all the tables have the formula = A&"."

etc. following this pattern

Hide column A of all these tables.

Feel free to use different columns than those in this example.

• Level 7

Smiley

I would approach this a little differently, but there are more similarities than differences.

I would change the names of the list tables from Table 1, Table 2, etc. to Jazz, Pop, etc.

Column A would have the song number, Column B, the song name.

In Column A, I would write: =IF(LEN(B)>0, ROW()-1 & "."

That's all the programming for the data tables. You can insert and delete and sort to your heart's content.

For the statistics I would have a table named "Summary". In the Summary table you would list the genres in Column A and would calculate the number of songs for each genre in Column B. The calculation of titles per genre would be:

=COUNTA(INDIRECT(A&"::B"))

The calculation for the combined total in the Footer row would be:

=SUM(B)

Here's a screen shot:

As you add genre tables, just add rows to the Summary table and expand the genre list, filling the count formula down.

I suggest that if you want to try my way, that you do it with a little sample table, like the one I used, to see if the formulas all work in Numbers 08. If anything fails, report it and we can try to adapt it to the old program.

Regards,

Jerry

• Level 1
iLife

Thank you again.

I'm afraid I am hopeless when it comes to formulas and therefore I am a complete beginner! Do I need to select the whole column before inputting the formula or just the top cell? I tried to put in the formula as written but I am guessing I need to add in the numbers of the rows or something because it wouldn't accept the formula?

Sorry, I am trying to read the manual to get some idea but it's like another language to me!

• Level 6
iWork

Smiley,

To enter a formula select the cell where you want the formula, then enter the formula (by typing or pasting).  e.g.  Jerry posted to:

In Column A, I would write: =IF(LEN(B)>0, ROW()-1 & "."

This means

In the first non-header cell of column A (cell A1) enter the text "=IF(LEN(B)>0, ROW()-1 & ".", "")"

It should look like:

shorthand for this is:

A2=IF(LEN(B)>0, ROW()-1 & ".", "")

then to fill in the rest of the column select cell A2, then grab (click and hold) the little circle at the the bottom-right of the selected cell, then drag straight down as needed, then release the mouse button.  You can also select cell A2, copy, then click cell A3, and scroll to the end of the column, then hold the shitft key, and click the last cell of the column, then paste.

it can be very helpful to copy the formulas directly from the posts, so click and hold starting at the "=" and then drag to the end end of the formula like:

then copy, the select the target cell in Number, then paste

• Level 7

Smiley,

To enter a formula, Select the first cell where you need the formula and type the formula, or Paste what you Copied from the text in the this discussion (the best way). Then, grab the little circle in the lower right corner of the cell you just modified and drag it down to the rest of the range where you need the formula. The little circle is called the Fill Handle.

The User Guide is a good thing to read. Having read the guide, the instructions you get here in the discussions will make more sense.

If the formulas I gave you happen to count the Header text as a Title, just subtract 1 from the count. I am not sure if the header will be counted in Numbers '08. It is not counted in Numbers '09, but that may be an improvement in the upgrade.

Jerry

• Level 1
iLife

Thanks so much everyone! Finally figured it out!

I realised that I was trying to put the formula into the first column but it wouldn't let me, so I created a new column as Badunit suggested, put the formula into that and then hid the first column and it's all good!

You have all been so helpful, I have learnt something from all of you!

• Level 7

Smiley,

Look especially for the desctiptions of Header Rows and Columns. You can regognize them because they default to a gray background fill. They are meant to hold titles, not formuals. If you don't need a Header Column, Delete it, don't Hide it. Or, begin with a Basic Table template that has no Header Column.

Jerry

• Level 1
iLife

I figured out how to remove the header column & am learning more each day! I'll get there one day

• Level 7

Smiley,

You will get there. If you find that you enjoy the app, do yourself a favor and upgrade to the 09 version. It's well worth the price of the download.

Jerry