Max,

It worked when I created a sample worksheet using Sheet1 and Sheet2 and
fictitional data. But when I copy the formula to the worksheet I'm using it
brings up a #VALUE! error. Can you help?

Here is some more details:

the worksheet's name I'm working is separate from the worksheet that
contains the number of employees per location code. The worksheet name that
has the data is HCount.xls and the sheet name is HC by Country. Column A has
the codes and column C has the number of employees.

So when I put the formula on my other sheet I get the error. Here is the
function I'm using:

=SUMPRODUCT((ISNUMBER(SEARCH('[HCount.xls]HC by
Country'!$A$11:$A$615,BG8)))*('[HCount.xls]HC by
Country'!$A$11:$A$615<""),'[HCount.xls]HC by Country'!$C$11:$C$615)


"Max" wrote:

One way
Assuming source data in Sheet1 cols A and B, from row 2 down
In another sheet,
you have the lookup values in A2 down
In B2:
=SUMPRODUCT((ISNUMBER(SEARCH(Sheet1!A$2:A$100,A2)) )*(Sheet1!A$2:A$100<""),Sheet1!B$2:B$100)
Copy down. Tested ok here.
If above helps, click YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
"Matheus" wrote:
I have one spreadsheet with the a column with a location code and another
column with the number of employees (Colum A2:A200 are the codes and B2:B200
are the number of employees).

On another spreadsheet, some locations are combined into one sigle row. So I
have a cell with the location codes separated by a coma (example: A2 = 100,
200).

Is there a way for me to use a function on B2 similar to a vlookup that will
look both values from A2 and add them up?