Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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'm looking at an excel work-book with potential definitions for a column(upto 135) referenced from an adjacent sheet.

E.g. Sheet 1: Col C (values 0-134 defined in Sheet 2 )

E.g. 0,1,8 Each row in Col C Sheet 1 may have any combination of the values separated by commas.

Imagine a workbook with two sheets named 'Student', and 'Team names' Each row in the sheet 'Team names' lists the ID of a team. A student may play on more than one team.

Say there are 3 teams they would appear on Sheet 'Team names' as


A2 = 20


Say now that there are 4 students whose appears on Sheet 'Student' as A1 = 13, A2=27, A3=43, A4=53.

Roll.No 13 Col B 10, 20, 30

Roll.No 27 Col B 30, 10

Roll.No 53 Col B 20

Roll.No 43 Col B 30, 20

The intent here is to setup a formula such that in the sheet 'Team names'

10 Col B 2

20 Col B 3

30 Col B 3

Sheet 2 must maintain statistics of the occurence of each value. This is done using COUNTIF. The issue here is that COUNTIF doesn't handle an embedded value too well. When so done, the reference to '1' in the above example won't appear.

How can this be done without resorting to a sub-routine?

share|improve this question
You mean without seperating the comma separated values into individual cells? – Paul Apr 5 '12 at 5:16
up vote 2 down vote accepted

If you have the list of values in sheet2 in A2 down try this formula for a count in B2


Copy formula down column

Assumes up to 1000 rows of data in sheet1, adjust as required


The above assumed that the data was separated by commas only (no spaces) but given your example, assuming the separators are [comma][space] together then try this revised version given your comments

=SUMPRODUCT(ISNUMBER(FIND(" "&A1&","," "&students!B$1:B$4&","))+0)

share|improve this answer
Let me give it a shot (+: Thank you!! – Everyone Apr 5 '12 at 17:49
Definitely on the right track. I took the liberty of assuming you'll be able to help me iron it out a little, and updated the question to make it clearer. – Everyone Apr 9 '12 at 19:58
@Everyone - I updated my answer to accommodate your changes – barry houdini Apr 9 '12 at 22:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .