Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a .csv open in Excel 2007 that looks like the following

1 566
1 566
1 566
2 341
2 341
3 566
3 566
3 566
3 566

There is a one-to-one relationship between Column A and B, but B is not unique (but can only take a small set of valid values). I wish to query how many id's (Column A) contain a particular property (Column B). If the example above ended before the "...", I would like to get as the output:

566 2
341 1

because the property 566 is owned by two id's (1 and 3) and the property 341 is only owned by the id 2.

I think this would be much easier on a relational database, but I have to use Excel.

share|improve this question
If you are able to modify the original data set you can use remove duplicates to get rid of the repeating entries then use countif to count the uniques – Pynner Jan 27 '13 at 16:49
up vote 0 down vote accepted

If your data is in A2:B100 then try this "array formula" in E2


confirmed with CTRL+SHIFT+ENTER

where D2 contains a specific property like 566

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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