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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]0201[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]0201[/TD]
[TD]21400[/TD]
[/TR]
[TR]
[TD]0202[/TD]
[TD]14000[/TD]
[/TR]
</tbody>[/TABLE]
...

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]0201[/TD]
[TD]33400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
Code	Value	Sheets	Code	Multi-sheet sumif
Sheet4
Sheet5
Sheet6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]201[/TD]
[TD="align: right"]38956.1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]202[/TD]
[TD="align: right"]5844.7[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]203[/TD]
[TD="align: right"]2222.8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]{=SUM(SUMIF(INDIRECT($H$2:$H$4&"!A:A"),I2,INDIRECT($H$2:$H$4&"!E:E")))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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