Suppose I have a Google Spreadsheet with this data:

```
A
----
foo
bar
bar
pim
pam
foo
foo
```

I am interested in knowing which is the value that appears more times. In this case, I would like to get `foo`

as it appears three times.

Searching, I found that in How to output the most common value and the number of occurrences of that value in spreadsheet? they supposedly solve this case, but even the initial step doesn't work for me. Indeed, if I do:

```
=COUNTIF(A1:A7;A1:A7)
```

It returns 0 when I put it on the right margin (B8, one row below the last item in column A).

More generally, my interest is to obtain a parallel table with a summary of the type:

```
valor ocurrencias
---------------------
foo 3
bar 2
pam 1
pim 1
```

To get the count of the most common value use

A way to get the frequency table sorted in descendingorder is mentioned in a previous answer . Here are some other ways:Dynamic tableA very cool feature of spreadsheets is pivot

tables, but this requires the data to have a header. You would add the values in the rows section and also in the values section using the`CONTARA`

(`COUNTA`

) function, then sort the rows in descending order based on`CONTARA`

.Statistical functionsThere are functions

`MODA`

(`MODE`

),`MODA.UNO`

(`MODE.SNGL`

) and`MODA.VARIOS`

(`MODE.MULT`

) but these require that the set of values be numbers. You could use these functions by "hardcoding" the type values by`TEXTO`

assigning them a type value`NÚMERO`

.Matrices / ArrayFormulaVariant of the solution provided in DjCrazi's answer , instead of using two formulas, only one is used, taking advantage of the use of arrays feature in Google spreadsheet and the

`ArrayFormula`

.Its parts are explained below.

Custom Functions, Plugins, ScriptIt is also possible to use

`JavaScript`

via`Google Apps Script`

either directly to create a custom function or script or indirectly via a plugin. To deal with this question of a formula I will not go into details in this answer.Below is an explanation of what is going on with

`=COUNTIF(A1:A7;A1:A7)`

and how the proposed array formula/ArrayFormula was arrived at.Don't let the cunic panda!In a new spreadsheet placing the data in

`A1:A7`

and the formula`=COUNTIF(A1:A7;A1:A7)`

in the cell`B1`

returns the value of`3`

, but if the data is moved, say one cell down, and the formula is left in its original position, it doesn't work.ChanfleIt turns out that we have been "victims" of one of the quirks of Google Spreadsheets, specifically the way it handles arrays. Putting the formula referred to in the question inside the function

`ArrayFormula`

returns an array of valuesI suspected them from the startTo get

the frequency table in descending orderusing a single formula we could use`=ArrayFormula(UNIQUE({A1:A7,COUNTIF(A1:A7,A1:A7)}))`

The good thing is that it even works if we move the data of its position

rechanfle`{A1:A7,COUNTIF(A1:A7,A1:A7)}`

This part returns a matrix of six rows by two columns. The first column includes the values to analyze and the second column the number of frequencies. NOTE: If your spreadsheet uses a comma as a decimal separator, use`{A1:A7\COUNTIF(A1:A7;A1:A7)}`

.`UNIQUE(...)`

Returns an array including unique rows.`ArrayFormula(...)`

Displays the array to the height and width required by the array resulting from its argument.recontrachanfleAnother alternative is to create a custom function using Google Apps Script or a plugin where someone else has already created this function for you. Please note that very soon plugins will no longer be available in the Chrome Webstore but will be available in the Works with Sheets section of the G Suite Marketplace and in Sheet templates that have a plugin linked if the plugin developer does so. what do you have to do for it.

Follow me the good onesToday the simplest and probably most convenient solution in a wide variety of circumstances is to use

pivot tables.Is it normal for it to`=COUNTIF(A1:A7;A1:A7)`

return`0`

?Returns 0 if the formula is in any other row that does not span the range with the values, but if it is in a row that spans the values it returns the number corresponding to the relative row, for example,

If the range is A1:A7 and the formula is placed in

`B8`

, returns 0`B1`

(foo), returns 3`B2`

(bar), returns 2For this you can use two functions:

`unique`

and`contar.si`

In cell

B1, with`=UNIQUE(A1:A7)`

you return the list with the values without repeating. Then, in cellC1with`=CONTAR.SI(A$1:A$7;B1)`

samples the number of times that are repeated in the first column.I don't know if this is what you are looking for but I got the following formula: