CODEWITHSUNDEEP
excelif-statementexcel-formula
Sam
Sam

Reputation: 11

IF Condition - Join Multiple Columns

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?

Upvotes: 1

Views: 87

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

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.

Upvotes: 1

pnuts
pnuts

Reputation: 59440

Seems a case for COUNTIF. Say your data is in A1:H1: 

=COUNTIF(A1:H1,"HEALTH")

Upvotes: 2

Related Questions