Excel dynamic array formula

Question

I have a workbook with 11 sheets for each month, and one summary sheet. On each sheet is a list of website in column A between A2 and A13, and in column B are some values taken from those website.

The individual Month sheets look something like this:

enter image description here

HOWEVER, it is possible that the information may be in a different order, so I have had to use MATCH to find the row where I want to get the data from.

The summary sheet looks something like this:

enter image description here

I want to build a formula that will sum all the values across the 11 worksheets for each website.

I tried to use INDEX and MATCH like this:

=INDEX(January!$A$2:$B$12,MATCH($A3,January!$A$2:$A$13,0),2)

Now this works for January and copies down for all the web-sites ok, but I'd like to SUM all the worksheets without adding the formulas?

How can I do something like this across all the worksheets?

It seems a bit naff to just add a plus sign and write eleven formulas in each cell

Thanks, but what if the value I am looking for isn't always on the same row (because different people have assembled the data)? — Our Man in Bananas
– Our Man in Bananas, Commented Dec 13, 2014 at 0:27

Tim Williams · Accepted Answer · 2014-12-13 01:15:02Z

2

Create a named range (eg) SHEETS containing your sheet names (in a column)

In B3 on your summary sheet:

=SUMPRODUCT(SUMIF(INDIRECT(SHEETS & "!A2:A11"), A3, INDIRECT(SHEETS & "!B2:B11")))

and fill down

answered Dec 13, 2014 at 1:15

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel dynamic array formula

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related