Sumif using a list of criteria

[bob2020]

I was wondering if there was a formula that I could use to sum a data file using the below columns as the lookup, I would also like to use another criteria in addition to this.

I.e If any of the numbers in column A appears in raw data file, sum the respective number in another column

A	B	C	D	E
56	16	20	98	87
4	2	7	9	65
695	546	110	848	464
	854	205	331	550
			484

<tbody>
</tbody>

Thanks in advance

 

[Special-K99]

Looks a simple request but I dont understand it. Please elaborate.

 

[bob2020]

[FONT=&quot]The columns B to E can be ignored as I don't think it's actually relevant to the formula, the actual formula I was thinking of was:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

However I would like the criteria1 to be replaced with a column of data.

Hope that makes more sense[/FONT]

 

[Special-K99]

I was expecting a more verbose description of the problem, possibly with expected output.

 

[bob2020]

I was expecting a more verbose description of the problem, possibly with expected output.

Column A has a number of references. I want the sumif formula to search through a large data dump and sum only the values that corresponds to the references in that list.

e.g. So for example, I want to look up references in column A in the data dump and sum the corresponding figures

Data dump

	A	B	C	D
1	References	Amount
2	506	50
3	4964	80
4	6416	42
5	56	10
6	25	8
7	56	5

<tbody>
</tbody>

The result would be 15 (Cell B5 + B7)

Hope that makes it clearer

Thanks

 

[Special-K99]

As you said just normal SUMIFS should do it

in Sheet1!F2
=SUMIFS('Data dump'!B$2:B$7,'Data dump'!!A$2:A$7,A2)
and copy down

 

[Fluff]

How about

Book1
	A	B	C	D	E	I
1	References	Amount				Ref
2	506	50		65		56
3	4964	80				506
4	6416	42
5	56	10
6	25	8
7	56	5
8
work 2

Cell Formulas
Range	Formula
D2	{=SUM(SUMIF(A2:A7,I2:I3,B2:B7))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bob2020]

Thank you both for your response.

I tried your formula but unfortunately it didn't work with my data. I played around with it a bit more and using =SUMPRODUCT(SUMIFS worked for me

 

[Fluff]

Glad you got it working & thanks for the feedback