Using a set of data as 'criteria' in a sumif

[jdro86]

Hi,

I'm trying to figure out how to use a set of data as criteria in a sumif formula.

Say I have 2 worksheets, Sheet1 and Sheet2.

Sheet1 has the range I want to sum (A:A) and sumrange (E:E)

Sheet 2 has large number of unique IDs (which would correspond to the range in Sheet1). I thought I would be able to do a sumif(A:A,[Sheet2]A1:Z24,E:E) and it would sum any of the numbers in my range that appear in [Sheet2]A1:Z24, but I'm getting a 0.

I also tried using an array {=SUM((A1:A1000=[Sheet2]A1:Z24)*(E1:E1000))} and I'm getting N/A for that.

Any ideas on how to make this work? I'm not familiar at all with VB and would need to do it strictly through formula.

Thanks in advance for the help

-Justin

[NBVC]

Maybe?:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A1000,'Sheet2'!A1:Z24,0)),E1:E1000)