filter & count unique text also!

[membio]

hi,
i have a row of 1000 random countries on a column for the month april

Portugal
France
England
Portugal
Portugal
Spain
Spain
Islands Pow Pow
...

i don't know wich new countries appear from time to time.
so i want a formula to count how many times the countries repeat on a monthly basis:



Portugal 3
france 1
england 1
spain 2
islands pow pow 1

 

[Aladin Akyurek]

hi,
i have a row of 1000 random countries on a column for the month april

Portugal
France
England
Portugal
Portugal
Spain
Spain
Islands Pow Pow
...

i don't know wich new countries appear from time to time.
so i want a formula to count how many times the countries repeat on a monthly basis:



Portugal 3
france 1
england 1
spain 2
islands pow pow 1

Have you considered running a pivoat table on your data?

 

[membio]

well, i managed to work out a solution with dynamic tables, i don't know if your pivot tables are the same as dynamic tables??

but i really need this to be as automatic as possible.

any ohter solutions ?

 

[Aladin Akyurek]

well, i managed to work out a solution with dynamic tables, i don't know if your pivot tables are the same as dynamic tables??

but i really need this to be as automatic as possible.

I don't know what "dynamic tables" are. Pivot tables are constructed using the pivot table tool Excel provides (on Excel 2010 see: Insert | PivotTable).
You can update a pivot table either manually or with VBA code that becomes active whenever the source data changes.

any ohter solutions ?

By means of a formula system, which can be be expensive.

 

[CWatts]

To aladin's point, the pivot table is a great option. You can set it up to automatically include new data and then you can either just right click it and refresh, or you can set a macro if you wanted to save a click or make it more intuitive for other users.