Sumif based on text within a sentence in a cell

[sanshah01]

Hi,

I would like to use sumif based on a text with occurs some where within
a sentence in a cell.

For eg... I would like to sumif the cells which contain the text B.O
which appears within a cell. I cannot use left, right or mid to isolate
the text since there is no fix position as to where the word B.O will
appear.

The sentence in the cell could be "FA Security B.O Report" or " FA
Other B.O. Report" etc..

Another problem is that sometimes it is mentioned as B.O or B.O. or
Branch office.

I would like to take a sum of all the cells any of the above text
appear within the sentence. Is there any formula or a combination of
macro and formula which will help me perform the same.

Regards
Sandip.

 

[Bob Phillips]

=SUMPRODUCT(--(ISNUMBER(FIND({"B.O","BO","Branch Office"},A2:A20))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[JE McGimpsey]

One way:

=SUMIF(A:A,"*B.O*",B:B)+SUMIF(A:A,"*Branch Office*",B:B)