Complex Array Formula Needed

[pliskers]

I have a list of items on Sheet 1 (column A), and a data table on Sheet 2 with item names in col A and values in col B.

I'd like an array formula that would deliver, in a single cell, the total of all records in column B on Sheet 2 that match ANY of the items in my list in col A Sheet 1.

I think I've described this clearly enough to hopefully not have to provide a data sample. Would greatly appreciate a sample formula. Many thanks!

 

[jim may]

Excel Workbook
	A	B
1	Item	Sum
2	abc	988
3	cde	931
4	fff	869
5	ggg	1345
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B2	=SUMIF(Sheet2!A:A,Sheet1!A2,Sheet2!B:B)

 

[Jerry Sullivan]

I'd like an array formula that would deliver, in a single cell, the total of all records in column B on Sheet 2 that match ANY of the items in my list in col A Sheet 1.

Jeff, When you write "total of all records in column B", do you want the Total Sum or the Total Count of matching rows?

If you want Sum, then you can use this Sumproduct formula to calculate that sum in one cell....

=SUMPRODUCT(--(ISNUMBER(MATCH(Sheet2!$A$2:$A$100,Sheet1!A:A,0))),Sheet2!$B$2:$B$100)

Adjust the number of rows for the range in Sheet2 to match your range size.