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.

 

[p45cal]

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 entr
(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 th
columns.
Therefore it is important that I only count the number of individual
(i.e.
Rows) and NOT the number of occurrences of 'Harm Reduction'.

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

Has anyone got any ideas, please??!!

Thanking anyone in advance,

Roy

*_ARRAY-ENTERED__*into a single cell:
=--(SUM(--(NOT(ISERR(SEARCH("Harm Reduction",D11:N11)))))>0)
in row 11 (addresses and which cell it goes in adjusted, of course, fo
where your top row is) will return a 1 if there's at least one instanc
of 'Harm Reduction' in that row, and zero if not.
Copy this formula down as far as you need. Then you just need to su
these results at the bottom of the column.

Variants:
1. You need not use the literal 'Harm Reduction' in the formula, yo
could refer to an absolute cell, say A1, which contains the string:
=--(SUM(--(NOT(ISERR(SEARCH($A$1,D11:N11)))))>0)
Then changing the contents of A1 to something else you want to coun
will update the result.

2. The formula is case insensitive, make it case sensitive by replacin
SEARCH with FIND

 

[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