Hello,
I have a list of 25,000 countries (duplicates), I want to count how many Australia, austira etc
I have managed to count sub totals but how to I gather these subtotal to make into a chart
You could create a pivot table from your data.
Make sure the list of countries has a column header.
Pivot table options are limited in LIbreOffice, so first add a helper column with the heading “count” to your table and set every cell in that column to 1.
Select a cell within the table and select “Insert Pivot Table” icon from the toolbar. This should give a Select Source dialog and highlight the range of the input table. Click on OK.
Drag the country column header name from Available Fields to Row Fields.
Drag count from Available fields to Data Fields.
Click on OK and a new sheet will be added to your spreadsheet document with a list of countries and a column that has the sum of count for each of the countries.
You can then create a chart from this table.
If you update the source table you can refresh the table using right click menu within the pivot table.
No need for helper-column.
Drag country-header to Row-fields
Drag country-header to Datafield, doubleclick and choose count to aggregate …
Thanks, I was trying right click and thought maybe LibreOffice pivot table just didn’t have the capability to change the aggregate function.
First make a list of all the countries ocurring in your data - complete and without any duplicates.
Then you can us COUNTIF or SUMPRODUCT to count the numbers of occurrences for each country.
What “subtotals” are you talking of? Creating a chart will need the same steps whether the labels are country names or anything else.
If your problem actually is to create the mentioned list:
a) Copy / Paste the column containingf th countries into an extra sheet.
b) Sort them alphabetically.
c) Filter them using the ‘Standard’ filter ‘Field name’ ‘-none-’, NO condition, Option ‘No duplications’ and 'Copy results to: (a free range)
d) Copy the result to the place where you want to do the counting and the (Sub-)summations.
(May I suppose that austira lies somewhere between Germany and Italy? Be careful with names directly typed in. They may contain differences like typos, trailing spaces, ?? else. Use the mentioned list, once created, for input validation.)
The Pivottable does all in one stroke!
please realize that we talk about a List of 25 k entries!
@karolus: “The Pivottable does all in one stroke!”
So it is - as long as it does “it” at all.
I did not judge the OQ knew exactly what she (m/f) needed. What ‘subtotals’? What about charting?
Next “question” may explain that there are alo dates and customers and everything.
OK. We may get at least the list of distinct countries in alphabetical order from a pivot table anyway.
And, of course, we wont forget to recreate it after a next country occurred first time in an appended row…