Ask Your Question

Counting a country list

asked 2016-10-17 14:43:54 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.


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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2016-10-17 19:11:15 +0200

mark_t gravatar image

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.

edit flag offensive delete link more



No need for helper-column.

Drag country-header to Row-fields
Drag country-header to Datafield, doubleclick and choose count to aggregate …

karolus gravatar imagekarolus ( 2016-10-20 13:44:46 +0200 )edit

Thanks, I was trying right click and thought maybe LibreOffice pivot table just didn't have the capability to change the aggregate function.

mark_t gravatar imagemark_t ( 2016-10-20 16:53:08 +0200 )edit

answered 2016-10-17 15:14:02 +0200

Lupp gravatar image

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.)

edit flag offensive delete link more

answered 2016-10-20 13:52:08 +0200

karolus gravatar image


The Pivottable does all in one stroke!

please realize that we talk about a List of 25 k entries!

edit flag offensive delete link more


@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...

Lupp gravatar imageLupp ( 2016-10-20 15:11:10 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-17 14:43:54 +0200

Seen: 531 times

Last updated: Oct 20 '16