Iain,

I guess that you mean

=SUMPRODUCT(--(ISNUMBER(FIND("depression",A1:A100))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Iain Halder" wrote in message
...
Hi,

Hope someone can help here and thanks if you can!

I want to sum individual text items within a cell


e.g.

CELL1 = depression, low mood, anxiety

CELL 2 = anxiety, personality

CELL THREE = bi-polar, suicidality

CELL FOUR = anxiety, depression, suicidality


There are hundreds of these cells all with various diagnosis in a
single long column and the idea is to count each individual occurrence
and then cross-reference them with other options like date, weekday,
weekend, seasonal cycles, etc.

I imagine for the latter I'd be using SUMPRODUCT (used successfully
before as advised by you guys in other previous questions).

However, initially I find countif, etc will only count the items if
there are single occurrences of the various terms above but will not
count them if they are part of a group of terms. In fact, countif and
sumif give me zero results.

How can one count the above variants individually even though they
appear within the same cell.

NOTE: The info is derived from an ACCESS '97 database initially and
this is how it comes out onto an EXCEL '97 spreadsheet.

Thanks again!!!

Iain Halder




o< Rescued Cats & Kittens Needing Homes o<
o< www.celiahammond.org o<
o< www.cat77.org.uk o<