search for text string within an array

[Guest]

Does anyone have any experience with searching for a text string within an array? i want the formula to return the sum of the values in one array whenever it sees a certain text string within longer text strings in a corresponding array.

i have been trying something similar to this:

{=sum(if(SEARCH("text",A1:A10,),1,0))}

and

{=sum(if(SEARCH("text",A1:A10,)>0,1,0))}

and

{=sum(if(SEARCH("text",A1:A10,),b1:b10,0))}

btw, none of these formulas seems to work.

i want the formula to sum the values in column b every single time it sees a certain text string within a longer text string in column a. For instance, In the table below, if I wanted the formula to sum the values in column B every single time it sees 'Apples', the formula would return 60. If the criteria was 'Oranges', the formula would return 50. I hope this makes sense. What do I do?

Column A Column B
======= =======
Apples and oranges 10
Apples 20
Apples and lemons 30
Oranges and Bananas 40

 

[hgrove]

Henrik wrote...

Does anyone have any experience with searching for a text
string within an array? i want the formula to return the sum of
the values in one array whenever it sees a certain text string
within longer text strings in a corresponding array.

i have been trying something similar to this:

{=sum(if(SEARCH("text",A1:A10,),1,0))}

and

{=sum(if(SEARCH("text",A1:A10,)>0,1,0))}

Try =COUNTIF(A1:A10,"*text*") .

and

{=sum(if(SEARCH("text",A1:A10,),b1:b10,0))}

...

Try =SUMIF(A1:A10,"*text*",B1:B10)

 

[Domenic]

Hi,

=SUM(IF(ISNUMBER(SEARCH("apples",A1:A4)),B1:B4))

entered using CONTROL+SHIFT+ENTER

Hope this helps!

 

[Guest]

Hi,

The formula by Domenic can be made more dynamic by asking for the input
(oranges or bananas) in some cell and then entering the following formula.
Cell B10 is the input cell. You may also use the Search string

SUM(IF(ISNUMBER(FIND(B10,$B$5:$B$8)),$C$5:$C$8))