Occurences of different words or terms in a spreadsheet

Occasional Contributor

Hello, my users spreadsheet has 70 names from 50 structures and 30 countries. I have tried pivot tables to count how many different structures and countries I have, can't do this automatically though. Would you have a way? Thanks infinitely, Jc

8 Replies

@Jc_Fadot Perhaps you can upload a file that accurately represents the structure (with sample data) of the spreadsheet you are talking about. If the information isn't confidential, why not upload the entire file?

Hello @Riny_van_Eekelen Yes, it is highly confidential, so I cannot share. I will try to make an avatar of its structure

So, here is a similar spreadsheet, I would like to make several statistics out of it: how many structures/individuals of level 1/2/3, from how many countries, from different domains.

To count how many in Tech, Art, from France, etc., this I can do, with filters, etc. What I cannot achieve is make statistics of similar occurrences. What I get for example is how many structures from, say, Canada, and what I want but can't get is how many structures from different countries/how many countries are represented. Thanks infinitely in advance!

@Jc_Fadot Not sure I understand what kind of statistics you are trying to prepare, but perhaps the examples I added to you workbook get you started.


Thanks very much, Riny!
This is a nice step forward, I think you did this with a pivot table. However, this table counts, correctly, the individuals, by category, and this is nice. But what I cannot achieve is count the structures by level, country and domain. Example, I have that many structures, all in all - here 6, but I counted them manually -, that many structures in art, agriculture, and so on, etc.

@Jc_Fadot Sorry! I don't follow. Can you, on the basis of the sample, manually create the reports you want to achieve? And what Excel version are you on? 

@Jc_Fadot hello, i used pivot table with filters for countries, level & domain, so when you filter for domain like agriculture you can know how many structures you have, hope this helps