Array formula across multiple worksheets

Wellsa

New Member
Joined
Apr 25, 2008
Messages
42
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,941
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 25, 2008
Messages
42
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,941
Office Version
  1. 365
Platform
  1. Windows
...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

New Member
Joined
Apr 25, 2008
Messages
42
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:


Code:
=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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,260
Messages
5,769,091
Members
425,516
Latest member
Melzebu

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
Top