Help needed with Vlookup and summing for large amounts of data

[carylinn]

Hi, I'm trying to figure out how to get Excel to look at a spreadsheet of thousands of lines of data, match the current variable on a new spreadsheet to all those that match in 1 column of the large data, and then look across to a specific column and add the numbers in that column. Is it possible to combine VLOOKUP and SUM into one command? All of the examples online include small data tables, and aren't exactly what I'm trying to do.

So for example, if my selected item has 5 matching items in the first column of the large spreadsheet of data, and those 5 matching items have a "1" in the next column over, I would like Excel to place a 5 (the total) back in the output spreadsheet.

Is this possible?

[#2]

@carylinn 

Small or large range could affect only performance, formulas work exactly the same way.

I'd use SUMIFS or SUMPRODUCT here.

[#3]

@Sergei Baklan 

So if SheetB has an item in A1, and I want Excel to look for matches of that item in SheetA, and then want it to add the numbers of those matching items in Column 30, how do I write that? I'm not sure how to tell it to find matches and then sum?

[#4]

@carylinn 

I missed, you'd like to copy matching items into some place or you'd like to sum them? If the latest, it's like

=SUMPRODUCT((A:A=Sheet1!A1)*Z:Z)
or
=SUMIFS(Z:Z,A:A,Sheet1!A1)