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


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]16[/TD]
[TD]20[/TD]
[TD]98[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]695[/TD]
[TD]546[/TD]
[TD]110[/TD]
[TD]848[/TD]
[TD]464[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]854[/TD]
[TD]205[/TD]
[TD]331[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]484[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]References[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]506[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4964[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]6416[/TD]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]56[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]25[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]56[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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