Google-sheets – How to display rows based on the filtered content of a column

google sheets

I have a table that has three columns:

| name | company | country |

so I have many people belonging to the same country, I wanted to make another table that made a list of unique companies, and another column that listed how many people belonged to that company like so:

| company | num users in company |

that was done using this formula

B1: Unique names
B2: =SORT(UNIQUE(FILTER( A2:A ; ISTEXT( A2:A))))

C1: # of times occurring
C2: =COUNTIF( A2:A ;SORT(UNIQUE(FILTER( A2:A ; ISTEXT( A2:A))) ))

How do I list the countries besides each company mentioned in a separate column, so the end result will look like this?

| company | num users in company | country |

Best Answer

Assuming you data is in Columns A:C. In several steps because easier (my excuse is that you have not clarified whether a company may be associated with more than one country):

In E1: =UNIQUE(QUERY(A:C,"select B,C where B is not NULL order by B,C",1))

to obtain an ordered list of unique company / country combinations.

In G1: Count, in G2 and copied down to suit: =countifs(B:B,E2,C:C,F2)

for the counting of names.

In I1: =query(E:G,"select E,G,F")

to rearrange the column order.

Related Topic