Sum a range if a cell contains a text string

[stuartw]

Hi everyone

The title explains it pretty well and expect this is a walk in the park for most of you!

If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)

Hope that makes sense.

Thanks as usual
Stuart

 

[barry houdini]

Something like

=IF(FIND("Navision",A1)>0,SUM(B1:B10),0)

...but if A1 doesn't contain the text "Navision" you get an error so change to......

=IF(ISNUMBER(SEARCH("Navision",A1)),SUM(B1:B10),0)

or

=COUNTIF(A1,"*Navision*")*SUM(B1:B10)

 

[rraghav]

Thanks a lot JonMo,
The formula you provided to search and sum saved me a lotta time.

Thanks,

Raghav

 

[Scott Huish]

If jonmo's answer gives you the result you want, then you should be able to use a SUMIF:

=SUMIF(A1:A100,"*Navision*",B1:B100)