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
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.
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":
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