Having real trouble with sumproduct across multiple worksheets with multiple criteria

[JRose1982]

Dear All

I have been helped out by some of the postings on this website already to get an idea about Indirect and how to link this with other functions to work across multiple worksheets with multiple criteria using sumproduct(sumif(indirect...))). These were great, thank you. I am now struggling with what I think should be more basic, but I cannot seem to make it work.

I am doing a survey of various housing development and am now aggregating the data and doing some analysis on it. I have produced a simplified example in tables below (in reality I am going to end up with about 100 worksheets, so I really want to avoid having to do something to each worksheet if I can) I am happy to multiple summations across the lot, and then work on the summary sheet, but I would rather not have to copy and paste a formula into 100 worksheets. I am also going to have to do this for multiple queries on different questions with a much larger data set, so need to try and understand how this is set up. There are three worksheets in the example (Development 1, Development 2, and Development 3). I am wanting to work out the mean GIFA per unit type across all the development, but splitting the data out by size of contractor and region and then amalgamating for a total on the summary sheet, so I will end up with a table with region on one axis and size of contractor on the other. I can amend for the different options, but it is the base formula I am struggling with. So lets try to find the average GIFA for Scotland / Large in the example. I think I want to use a formula similar to the one shown on https://www.extendoffice.com/documents/excel/2458-excel-multiply-two-columns-and-then-sum.html under 2.1 but using indirect references to point at the multiple worksheets.

I think on the summary sheet I need to do something like where F1:F3 is my list of tab names, B5:E5 is unit GIFA, B4:E4 is number of each type of unit, B1 is constraint for size of developer and B2 is constraint for region.

=sumproduct((indirect("'"&F1:F3&"'!B5:E5)*(indirect("'"&F1:F3&"'!B1)="Large"), (indirect("'"&F1:F3&"'!B4:E4)*(indirect("'"&F1:F3&"'!B2="Scotland")))

I am getting a message that my formula is incorrect and it is highlighting the first "'" in the first constraint (which I have made bold in the formula above.) This is probably not the only error!

Summary sheet

[TABLE="width: 600"]
<tbody>[TR]
[TD]Average (mean) GIFA per house type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Development 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Development 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Development 3[/TD]
[/TR]
</tbody>[/TABLE]

Development 1
[TABLE="width: 524"]
<tbody>[TR]
[TD]Size of Contractor[/TD]
[TD]Large[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]Scotland[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit type[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Number of types of unit on development[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Unit GIFA[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

Development 2
[TABLE="width: 524"]
<tbody>[TR]
[TD]Size of Contractor[/TD]
[TD]Large[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD="colspan: 2"]North East[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit type[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Number of types of unit on development[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Unit GIFA[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[/TR]
</tbody>[/TABLE]

Development 3
[TABLE="width: 531"]
<tbody>[TR]
[TD]Size of Contractor[/TD]
[TD]Medium[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Region[/TD]
[TD]North East[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unit type[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Number of types of unit on development[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Unit GIFA[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Looks to me like you are not closing the last part of each SP argument?
=sumproduct((indirect("'"&F1:F3&"'!B5:E5)*(indirect("'"&F1:F3&"'!B1)="Large"), (indirect("'"&F1:F3&"'!B4:E4)*(indirect("'"&F1:F3&"'!B2="Scotland")))
=sumproduct((indirect("'"&F1:F3&"'!B5:E5")*(indirect("'"&F1:F3&"'!B1")="Large"), (indirect("'"&F1:F3&"'!B4:E4")*(indirect("'"&F1:F3&"'!B2")="Scotland")))

 

[JRose1982]

Hi FDibbins

Whilst, I think you might be right, I am still getting an error with the "'" when I use your formula. I think you might be right about the brackets, as I said quite likely to be multiple errors! Does anybody know if my logic is correct on how to set this up and then solve the errors in the formula?

Julian

 

[FDibbins]

I did some testing (without data) and found you had some extra ) in there, This gave me an error answer, probably because I had no data, but give it a shot....
=SUMPRODUCT(INDIRECT("'"&F1:F3&"'!B5:E5")*INDIRECT("'"&F1:F3&"'!B1")="Large", INDIRECT("'"&F1:F3&"'!B4:E4")*INDIRECT("'"&F1:F3&"'!B2")="Scotland")