I have a table of people, with a column describing medical conditions (comorbidities) as free text. In another sheet I have several tables, each a different category of condition, with conditions that match that category as well as various spellings of said conditions.
i.e. table header is "respiratory" and then the rows are asthma, copd, pneumonia etc.
I want to count how many people satisfy each condition category by matching at least one of the conditions from the rows (for each category).
At the moment I have:
=SUM(COUNTIF(PERSONS[COMORBIDITIES],"*"&Table17[Respiratory]&"*"))
But this is counting for each and every keyword, and returning (for example) 5 respiratory conditions for one person, when I only want it to be TRUE (or 1) that YES this person has one of those conditions in that category.
Apologies if I haven't explained it very well.
Aden