Counting words in cells with multiple conditions

**Katie1982** · 12-09-2011, 06:35 PM

PLEASE HELP!

I have four columns of diagnoses per client, and I want to count how many have a substance abuse/dependence diagnosis, without double counting those with more than one substance diagnosis. The below example row has two substance diagnoses (cannabis and alcohol), but I only want to count it once. I have tried to use the below formula, but it just returns all 1's. Is something wrong with this formula, or do I need a completely different approach.

Example row: H3: Depressive Disorder NOS ADHD (Combined Type), I3: Cannabis Dependence, J3: Alcohol Abuse, I3: Oppositional Defiance Disorder

=IF(AND(OR(H3="*Cannabis*",H3="*Alcohol*",H3="*Substance*",H3="*Opioid*",H3="*Amphetamine*",I3="*Cannabis*",I3="*Alcohol*",I3="*substance*",I3="*Opioid*",I3="*Amphetamine*",J3="*Cannabis*",J3="*Alcohol*",J3="*Substance*",J3="*Opioid*",J3="*Amphetamine*",K3="*Cannabis*",K3="*Alcohol*",K3="*Substance*",K3="*Opioid*",K3="*Amphetamine*")),1,0)