search a code in multiple sheets and return the sum of value of that code

[seeker123]

Hi everyone,

I have about 30 sheets that contain tables like below

Code	Value
0201	12000
0201	21400
0202	14000

<tbody>
</tbody>

...

I have a summary table containing all codes in a different workbook .what I want to have is the sum of values of each code in the summary table.
please bear in mind that they are on several sheets and I want the sum of them.(All codes are in column a and all values are in column E)
for example

Code	Value
0201	33400

<tbody>
</tbody>

Thanks a lot in advance

 

[Eric W]

If you have a list of the sheets, you can try this:

	A	B	C	D	E	F	G	H	I	J
1	Code				Value			Sheets	Code	Multi-sheet sumif
2	201				0.5			Sheet4	201	38956.1
3	201				0.6			Sheet5	202	5844.7
4	202				0.7			Sheet6	203	2222.8
5	203				0.8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula J2 {=SUM(SUMIF(INDIRECT($H$2:$H$4&"!A:A"),I2,INDIRECT($H$2:$H$4&"!E:E")))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Tetra201]

I would suggest:
- adding single quotes before and after the $H$2:$H$4 (...INDIRECT("'"&$H$2:$H$4&"'!A:A")...)
- using SUMPRODUCT instead of the SUM to avoid CSE

 

[seeker123]

Thank you, Eric, it worked perfectly
(although it take quite a time for me to understand it)

 

[seeker123]

Thank you Tetra useful tips