SUMIF across multiple sheets

[JoeEv]

Hello

I am trying to create a sumifs across multiple sheets;

The Summary tab is as follows:
[TABLE="width: 373"]
<tbody>[TR]
[TD][/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[TD]C3[/TD]
[TD]C4[/TD]
[TD]C5[/TD]
[/TR]
[TR]
[TD]R1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]R2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]Data 1[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]R4[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R5[/TD]
[TD]Data 2[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]R6[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R7[/TD]
[TD]Data 3[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
</tbody><colgroup><col><col><col span="4"></colgroup>[/TABLE]

The sum range of the data is cells K22:K29 across the sheets, the first criteria (Data 1 - B6) is in cells A22:A29 across the sheets, the second criteria (1 - C4) is the name of the various sheets and also in cell B12 across the sheets.

I've read about making a name range for the names of the tabs (1-4) which I have done and called Sheets. I've tried the following formula but returns #VALUE! and alternatives but can't seem to bring the data in - i think it may be because first criteria is vertical and the second horizontal?

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!K22:K29"),INDIRECT("'"&Sheets&"'!A22:A29"),B6,INDIRECT("'"&Sheets&"'!B12:B12"),C4))

Any ideas on what's going wrong?

Cheers