7 Replies Latest reply: Nov 29, 2012 11:48 PM by Barry
edoardofrombologna Level 1 Level 1 (0 points)

I'm very very new to spreadsheets, so apologize for dumb question.

I have a sheet containing bibliographical data from which I'd like to get charts that analyze the contents of single or multiple columns (see screenshot).

Basically columns contain text strings (authors, dates, geographical area and so on). Is it possible to auto-count occurrences of all text strings in a column and pass the results to a chart? I guess there should be a formula to do this but, as I'm pretty new, I'm totally unaware of formulas power.

What I'm manually doing now is, let's say, sort "author" column, count the rows for a single author and transcribe the data (author, occurrences) in a new sheet. After having done that I create the chart from the new sheet. The point is that it is a bit tedious (200+ entries) and if I have to add new data to the main sheet, I have to update data manually in the other sheets.

So the workflow, I think, should be: count all text strings and pass the data to a chart

Can you help me?iwork numbers.jpg

Numbers, OS X Mountain Lion (10.8.2)
  • Wayne Contello Level 6 Level 6 (15,455 points)



    You can chart the count of each item so long as you have the count.  To get the count you need a list of each unique... or distinct... (Jerry or Barry will clarify    ) item in the author (or what ever column).... I am focusing on the Author then you can expand to other columns.



    To get the list of distinct authors add a new column to your book table.


    Screen Shot 2012-11-27 at 12.59.10 PM.png


    I called the column "Distinct Author".  This column lists each author only once.


    B2=IF(IFERROR(MATCH(A2, $A$1:A1, 0), 0)>0, "",A2)

    select B2 then fill down as needed


    the select column B (except for the header row), copy.


    Now create a new table and give it the title "Author Summary":

    Screen Shot 2012-11-27 at 1.01.12 PM.png


    Make the first row and the first column a header

    now select cell A2, then paste


    now sort column A by author as needed


    now enter the formula:

    B2=COUNTIF(Book List :: A, "="&A2)


    select B2 and fill down as needed




    to make a pie chart select the cells in column B (the "Count" column), then select the pie chart from the "Chart" menu in the tool bar

  • edoardofrombologna Level 1 Level 1 (0 points)

    Hi Wayne,

    thanks for the quick help: I tried the first part:

    =IF(IFERROR(MATCH(A2, $A$1:A1, 0), 0)>0, "",A2)


    but I got a syntax error. Don't know much about "numbers" but, could it be a problem of localization of my copy (which is in italian)?

    Thanks a lot


  • Wayne Contello Level 6 Level 6 (15,455 points)

    try using the following:


    =IF(IFERROR(MATCH(A2; $A$1:A1; 0); 0)>0; "";A2)


    i.e. change "," to ";"

  • edoardofrombologna Level 1 Level 1 (0 points)

    Thanks Wayne, that worked perfectly!!!!

    Wayne Contello wrote:


    try using the following:


    =IF(IFERROR(MATCH(A2; $A$1:A1; 0); 0)>0; "";A2)


    i.e. change "," to ";"


    Sorry, some questions to understand how it general works:

    - if I reorganize the main table (Book List), formulas won't work anymore as 'coordinates' of single cells change, isn't it?

    - if I edit a cell (let's say changing an author) this get reflected in the author table changing the count; on the contrary if I add (in the main table) a NEW author I have to re-do all the author table, am I correct?

    - if I hide the "Distinct" column (in your example) will the auto-fill in the other tables continue to work? (made a try and apparently works


    Ah, BTW: I think that in localized Numbers versions (mine is in italian) all 'commands' are also localized; I'm saying it for non-English speakers and it's quite annoying as 99% of help you can get is in English meaning a non-English speaker will have to 'localize' all 'commands' which in some cases in not so self-evident...


    Finally: thanks again Wayne


  • Barry Level 7 Level 7 (29,215 points)

    Buongiorno Edoardo,


    You worte:

    "I'm saying it for non-English speakers and it's quite annoying as 99% of help you can get is in English meaning a non-English speaker will have to 'localize' all 'commands' which in some cases in not so self-evident..."


    Most, but not all of the responders to questions in this user-to-user community have English as their native tongue. Their copies of Numbers, which they use to test their answers, is localized to one of the English speaking regions, so they will not be familiar with other localizations of Numbers and the function names for the functions it supports.


    If your question is in English, the assumption of those responders is often that your copy of Numbers is also loclised to an English language region, and that you are expecting an answer in English, and appropriate to one of those regions.


    If your question is in a language other than English, many of the responders, Wayne included, will turn to Google Translate (or other similar services) to translate the question into English and the answer back into the original language in which the question was posted. Computer generated translations from online services are seldom 100% accurate, so these will generally be accompanied by a note that the response was translated, and naming the service used.


    I'm not sure that you can expect better 'service' than that.


    Google translation follows:

    Traduzione di Google segue:


    La maggior parte, ma non tutti i soccorritori di domande di questo user-to-user comunità hanno l'inglese come lingua madre. Le loro copie di numeri, che vengono utilizzate per testare le loro risposte, è localizzato in una delle regioni di lingua inglese, in modo da non avere familiarità con localizzazioni altri numeri ed i nomi delle funzioni per le funzioni che supporta.



    Se la tua domanda è in inglese, l'assunzione di tali risponditori è spesso che la copia di numeri è anche loclised ad una regione di lingua inglese, e che ci si aspetta una risposta in inglese, e di conferire ad uno di tali regioni.



    Se la tua domanda è in una lingua diversa dall'inglese, molti dei soccorritori, Wayne incluso, si rivolgono a Google Translate (o altri servizi simili) per tradurre la domanda in inglese e la risposta di ritorno nella lingua originale in cui è stato postato la domanda . Le traduzioni automatiche generate dai servizi online raramente sono precisi al 100%, per cui questi saranno generalmente accompagnate da una nota che la risposta è stato tradotto, e la denominazione del servizio utilizzato.



    Io non sono sicuro che ci si può aspettare una migliore 'servizio' di questo.




  • edoardofrombologna Level 1 Level 1 (0 points)

    Hi Barry,

    I think there's a misunderstanding, probably due to my lack of fluency in English.

    I have not the habit of complaining with somebody who's kindly helping me for free. So, my apologies to Wayne if I sounded kind of unthankful.

    My complaint (or better: my feedback) was on Apple side not on user's community.

    I'm new to numbers so take it for what it's worth: I basically think that function commands would be more practical if not localised for the following reasons:

    - I see functions nearer to 'programming language' than to user interface

    - the more universal a programming language is, the better

    - that being the case, as English is the universal language in tech world, it would be more practical to have functions directly in English even in localized versions, just because, for example, it would allow a more direct exchange of knowledge in user's community


    Hope this helps to clarify what I meant and...


    Finally: thanks again Wayne


    Best Regards


  • Barry Level 7 Level 7 (29,215 points)

    HI Edoardo,


    Thanks for the clarification. Those are legitimate observations.If you want them to get to Apple, I'd suggest using the feedback channel. There's a link to it in the Numbers menu. In English versions, it's named Provide Numbers Feedback.


    Picture 24.png