Having real trouble with sumproduct across multiple worksheets with multiple criteria

JRose1982

New Member
Joined
Sep 15, 2017
Messages
2
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

Average (mean) GIFA per house typeDevelopment 1
Development 2
Development 3

<tbody>
</tbody>

Development 1
Size of ContractorLarge
RegionScotland
Unit type1234
Number of types of unit on development51022
Unit GIFA100200150500

<tbody>
</tbody>

Development 2
Size of ContractorLarge
RegionNorth East
Unit type5678
Number of types of unit on development4745
Unit GIFA8017520075

<tbody>
</tbody>

Development 3
Size of ContractorMedium
RegionNorth East
Unit type9101112
Number of types of unit on development51022
Unit GIFA100200150500

<tbody>
</tbody>
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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")))
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top