Text search across spreadsheets with SUM in adjacent cell

[26tom]

Is it possible to perform an IF statement in one spreadsheet based on text conditions met in a different spreadsheet?

For example one spreadsheet has a list of text strings with adjacent values in such as:

2009_9_12_months_E26_it_IT 1001
2009_9_12_months_E26_nl_BE 90
2009_9_12_months_E26_nl_NL 540
2009_9_12_months_E26_no_NO 118

However, the placement of the text string and adjacent value is not consistent and can change.

My problem is I have another spreadsheet that has a row labelled Dutch and I need to SUM the values from the cells adjacent to the rows containing the text string "E26_nl" in the first spreadsheet. Something along the lines of:

IF any rows of Column A in spreadsheet1 contains the value "E26_nl"
Then SUM the adjacent values in a cell in spreadsheet2.

In this case for example I would be looking to get the value 630 in a cell in the 2nd spreadsheet.

Anyone know if this is remotely possible or if I'm just clutching at straws?

Any help appreciated. Cheers.

 

[Airfix9]

Use SUMIF.

Assume that data is in Sheet1 and in columns A and B. Also, the code you are looking for (E26_nl) is in Sheet2, cell A2

=SUMIF('Sheet1'!$A:$A,"*"&A2&"*",'Sheet1'!$B:$B)

That should do it.

 

[26tom]

Use SUMIF.

Assume that data is in Sheet1 and in columns A and B. Also, the code you are looking for (E26_nl) is in Sheet2, cell A2

=SUMIF('Sheet1'!$A:$A,"*"&A2&"*",'Sheet1'!$B:$B)

That should do it.

Thanks for your help.

Don't think it makes a difference but the data is in two different spreadsheets not two different sheets within a spreadsheet.

The data I am looking for and the text string is all in the first spreadsheet, but I need to use it to put a value in the 2nd spreadsheet.

So I was thinking something along the lines of the below in the relevant cell in the 2nd spreadsheet to check whether the text string is present and then perform a SUM if the value is true

=IF(ISNUMBER(SEARCH("*E26_nl*",Spreadsheet1!A1016:A1046)),"do the sum of the adjacent values matching the search","")