Text search across spreadsheets with SUM in adjacent cell

26tom

New Member
Joined
Mar 11, 2009
Messages
2
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.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
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

New Member
Joined
Mar 11, 2009
Messages
2
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","")
 

Watch MrExcel Video

Forum statistics

Threads
1,129,814
Messages
5,638,493
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top