I need to tally the number of times a term appears. Unfortunately, the terms were not well organized so a term may appear in more than one column - so I can't use just =If(A1="HEALTH",1,0) because HEALTH appears in multiple columns A, B, C etc.
I've tried nesting - =IF(A1="HEALTH",1,IF(B2="HEALTH",1,0)) - but haven't had much success there either.
Maybe my formula's wrong? Or should I look to a different condition in Excel and if so, which?
Seems a case for COUNTIF. Say your data is in A1:H1:
=COUNTIF(A1:H1,"HEALTH")
Depending on how you want to count your input cells, and assuming your input data is in A1:F1, you may want (1)
=COUNTIF(A1:F1,"HEALTH")
as posted by pnuts, or (2)
=COUNTIF(A1:F1,"*HEALTH*")
This version also counts cells which contain HEALTH with other characters as well.
An alternative for formula (1) is
=SUM(IF(A1:F1="HEALTH",1,0))
entered as an array formula with Ctrl+Shift+Enter. Similarly for formula (2).
There are other more complex cases you may want to consider: a) cells containing HEALTH more than once, b) HEALTH only as whole word, etc.
