G
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
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