SUMIF across multiple sheets

[JoeEv]

Hello

I am trying to create a sumifs across multiple sheets;

The Summary tab is as follows:

	C1	C2	C3	C4	C5
R1		1	2	3	4
R2
R3	Data 1	Formula	Formula	Formula	Formula
R4
R5	Data 2	Formula	Formula	Formula	Formula
R6
R7	Data 3	Formula	Formula	Formula	Formula

<tbody>
</tbody><colgroup><col><col><col span="4"></colgroup>

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