Counting Individuals not Occurrences in Excel 2007

[Royegg]

I have a worksheet containing text items in multiple columns. Each row
represents an individual (n=189). I am trying to count the number of
individuals who have at least one instance of a particular text entry (e.g.
Harm Reduction).

Now there are approximately 10 columns in which 'Harm Reduction' could
appear but not all individuals will have this text entry across the columns.
Therefore it is important that I only count the number of individuals (i.e.
Rows) and NOT the number of occurrences of 'Harm Reduction'.

I have been trying to use SUMPRODUCT but that only seems to count occurences.

Has anyone got any ideas, please??!!

Thanking anyone in advance,

Roy

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Otávio Alves Ribeiro]

Hi there.
I will assume that you have text items in columns B to K, say, in the range
B2:K5 and you want to know how many rows contain some text in any of these
columns (each row should represent an individual).
That said, you may want to adapt the following formula to your actual range:

=SUM(SIGN(MMULT(IF(NOT(ISERROR(SEARCH("Your Search
Text",B2:K5))),1,0),TRANSPOSE(SIGN(COLUMN(B2:K2)))))) SHIFT + CTRL + ENTER

On the above formula, you should note:
1. It is an array formula, hence the need of the SHIFT + CTRL + ENTER key
combination to finalize it.
2. B2:K2 is the first row of your ten column data.
3. B2:K5 is the entire range of your data.
4. This solution assumes that your columns are contiguous.

Best regards,
Otávio