I am working with pivot tables from Google Spreadsheets. It works well for me and I am extracting interesting data, basically thanks to what I learned in How do I see what is the most common value (mode) of a column? .

This time I have the following table of books read

 A  |  B    |   C   |  D   |   E    |   F
año | libro | autor | nota | idioma | país autor

With entries of the type

 A   |  B               |   C         | D | E   |   F
-------------------------------------------------------------
2004 | Hamlet           | Shakespeare | 8 | ES  | Reino Unido
2005 | Crimen y castigo | Dostoievski | 9 | CAT | Rusia
2011 | Dersu Uzala      | Arseniev    | 8 | EN  | Rusia 

Now I want to create a pivot table that aggregates the countries the author comes from, tells me how many books I've read from each country, and what average grades they have.

In this specific case it would output something like:

país        | #libros | nota media
----------------------------------
Rusia       |    2    |     8,5
Reino Unido |    1    |     8

I have been able to do the first part, that of:

país        | #libros
---------------------
Rusia       |    2
Reino Unido |    1

With a pivot table where I simply choose the column F:F. However, to do the mean of the grades I also need the column Dand I don't know how to convert F:Fit to something like D:D;F:F. What would be the way?

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 fooas 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

If there is "some value" in cell F1, it is required to obtain that data as a concatenation of cells B3 and B4, where B3="F" and B4="1".

attempt :

Use the & operator to perform concatenation.

="="&B3&B4

Resulting in "=F1" as a string plus not the data located in said cell.

I have a tabular report, also called a matrix or "crosstab". For example, the name of the customer is indicated in the column headers, the name of the product in the row headers and the number of products sold to each customer in the intersection.

+---+------------+-----------+-----------+-------- ---+
| | To | B | C | D |
+---+------------+-----------+-----------+-------- ---+
| 1 | | Client 1 | Client 2 | Client 3 |
| 2 | Product 1 | 4 | 2 | 6 |
| 3 | Product 2 | 7 | 8 | 6 |
| 4 | Product 3 | 6 | 3 | 5 |
| 5 | Product 4 | 3 | 3 | 8 |
+---+------------+-----------+-----------+-------- ---+

I require to pass the data to plain table format, i.e. one column for row headers, one column for column headers and one column for values, as shown below:

+----+---------------------+---------------------- --+-----+
| | To | B | C |
+----+---------------------+---------------------- --+-----+
| 1 | Row Headers | Column headers | Values ​​|
| 2 | Product 1 | Client 1 | 4 |
| 3 | Product 2 | Client 2 | 8 |
| 4 | Product 3 | Client 3 | 5 |
| 5 | Product 4 | Client 1 | 3 |
| 6 | Product 1 | Client 2 | 2 |
| 7 | Product 2 | Client 3 | 6 |
| 8 | Product 3 | Client 1 | 6 |
| 9 | Product 4 | Client 2 | 3 |
| 10 | Product 1 | Client 3 | 6 |
| 11 | Product 2 | Client 1 | 7 |
| 12 | Product 3 | Client 2 | 3 |
| 13 | Product 4 | Client 3 | 8 |
+----+---------------------+---------------------- --+-----+

How can I achieve this using google spreadsheet functions?

Clarifications

Question inspired by How do you create a “reverse pivot” in Google Sheets? - Stack Overflow and personal experience.

Environment:

• Google Chrome Web Browser Version 47.0.25
• Google spreadsheets spreadsheet

Situation

I am trying to make cell I1 change color only when IJ is greater than J1, when choosing the condition the option to relate other cells does not appear

I try to configure using the "Custom formula is" option without success.