Array formula across multiple worksheets

[Wellsa]

I have a worksheet which contains a list of items (1-44) in one column, and a sensitivity trigger in the next column where I can turn the item on or off. I want a summary showing the movements in each item if they are turned on.

There is a worksheet for each item (called simply 'Item 1' etc), and a summary worksheet which is the same format as the individual item worksheets (which contains a number of rows requiring population and for the years 2010 to 2018 across columns).

I need a formula which will sum across each worksheet but ignore the movements if the trigger is turned off.

I'm pretty sure array fomulae can't achieve the result I require directly but I was hoping I could play with the indirect function etc and get what I want.

Hopefully this makes sense, any help would be appreciated.

 

[Domenic]

Somewhat unclear... Can you provide a small sample of data for one of the worksheets, along with the actual expected results? Base the actual results on the sample data itself. A solution can then easily incorporate other worksheets in the workbook.

 

[Wellsa]

I'm struggling to add a sample sorry, but in simple terms if each worksheet (including the summary) was populated with 4 rows of data (called Data 1-4), with only one column of information (therefore my used range being A1:B4) and each value was £100, there were 4 worksheets (called Item 1-4) and each containing the same values, if in my other worksheet Items 1-4 were turned on then my total in the summary worksheet for each data would be £400, with a total ovetall of £1600.

So in the summary sheet, cell B1 I could add a formula like follows which would return the value of £400 for each data row).

=SUM('Item 1:Item 4'!B1)

However, if I turned item 4 off my summary worksheet should only show £300 for each data and £1200 in total.

If it is easier I would have a cell in each worksheet which would identify if the item was to be included in the summary calculations (A6 for example).

I could do this automatically by creating an input summary worksheet but I would need 44 (the number of items i.e. worksheets) * however many rows there are, I could then use an array formula to include or exclude the variance from there and sum all of the items from there.

However due to the volume of data I wanted to add a formula with this functionality into one cell in the summary worksheet.

Hope this helps, I have a fairly easy way to do this without much manual labour anyway but I prefer searching for better methods.

 

[Domenic]

...if in my other worksheet Items 1-4 were turned on...

Not sure what you mean by that...

If it is easier I would have a cell in each worksheet which would identify if the item was to be included in the summary calculations (A6 for example)

If, for example, A6 contains 'Yes", indicating that the sheet needed to be included in the summary calculations, then maybe...

=SUMPRODUCT(--(T(INDIRECT("'Item "&ROW(INDIRECT("1:44"))&"'!A6"))="Yes"),SUMIF(INDIRECT("'Item "&ROW(INDIRECT("1:44"))&"'!B1:B4"),"<>"))

...I have a fairly easy way to do this without much manual labour anyway but I prefer searching for better methods.

Might be the way to go...

 

[Wellsa]

Sorry, I hadn't realised you had replied as I was not notified.

I managed to get what I wanted with a bit of playing around using the following formula in case anyone else finds themselves in a similar predicament:

=SUMPRODUCT(SUMIF(INDIRECT("'Item "&ROW(INDIRECT("1:44"))&"'!"&ADDRESS(13,2)),"Included",INDIRECT("'Item "&ROW(INDIRECT("1:44"))&"'!"&ADDRESS(ROW(),COLUMN()))))

Where I needed to sum across worksheets named 'Item 1' to 'Item 44', cell B13 in each worksheet indicated whether the item was to be included or not, and because the summary worksheet is set up exactly the same as the 'Item' worksheets I did not need to specify a row or column reference in the final part of the formula and could just just the location of the active cell.

Thankfully this works anyway, hopefully it can help someone else down the line.

Thanks for your help.