1

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 |

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.