Skip navigation

How do I add a cell count to the data already in my tables in Numbers '08?

335 Views 12 Replies Latest reply: Nov 27, 2012 11:28 AM by Jerrold Green1 RSS
smileynd Level 1 Level 1 (0 points)
Currently Being Moderated
Nov 26, 2012 6:36 AM

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)
  • Badunit Level 6 Level 6 (10,765 points)

    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

     

    The count will adjust corrrectly when you add or delete rows.

     

    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.

  • Badunit Level 6 Level 6 (10,765 points)

    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.

  • Jerrold Green1 Level 7 Level 7 (28,195 points)

    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:

    Screen Shot 2012-11-26 at 1.11.22 pm.png

    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

  • Wayne Contello Level 6 Level 6 (12,650 points)

    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:

    Screen Shot 2012-11-26 at 2.22.17 PM.png

     

    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:

    Screen Shot 2012-11-26 at 2.18.17 PM.png

     

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

  • Jerrold Green1 Level 7 Level 7 (28,195 points)

    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

  • Jerrold Green1 Level 7 Level 7 (28,195 points)

    Smiley,

     

    Keep reading that User Guide.

     

    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

  • Jerrold Green1 Level 7 Level 7 (28,195 points)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.