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:

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:

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

Answer 1

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