Using vlookup/indirect/sumif to return sum of results from variable reference tab

[BS1234]

I have a workbook with an index, that returns all of my tab names in column B (=INDEX(listsheets,A6). This is then used to search specific data (row 3) within each tab using the formula =VLOOKUP($E$3,INDIRECT("'"&B6&"'!"&"b3:F500"),5,0).

The data on row 3 of my index tab, is searched for in column B of my following tabs (200+), the formula then returns the value of column F of the matched cell in B.

This formula works, until there are more than one matching cells in column B of my following tab, then it only returns one of the figures.

I need it to return the sum column F of all matched cells from column B. I've tried to build in the SUMIF function, but can't get it to work with my INDIRECT.

Also when there are no results the current formula returns "#N/A", i would ideally like this to return "0".

Could someone please help?

Thank you

 

[Eric W]

I haven't tested this yet, since I'd have to create a sample workbook, but the formula should be like this:

=SUMIF(INDIRECT("'"&B6&"'!B3:B500"),E$3,INDIRECT("'"&B6&"'!E3:E500"))

Try that, and if it doesn't work, I'll build up the test sheets.

 

[DanteAmor]

Try this

Book1
	A	B	C	D	E	F	G	H
1
2
3					X1	X2	X3	X4
4	1	Index
5	2	A			48	54	60	0
6	3	B			0	0	0	0
7	4	C			0	0	0	0
8	5	D			0	0	0	0
9	6	E			0	0	0	0
Index

Cell Formulas
Range	Formula
E5:H9	E5	=IFERROR(SUMIF(INDIRECT("'"&$B5&"'!B3:B500"),E$3,INDIRECT("'"&$B5&"'!F3:F500")),0)