I'm trying to get the total result of multiple sheets where I have, in column A, a dropdown list with values `1` to `3` for example.

In total I have 12 sheets (one for each month) and want from all the sheets the result of all the values total in one cell for value1 1 for value2 and so on. An example:

I came up with this solution.

## Formulae

``````(1) =TRANSPOSE(ARRAY_ROW(JAN!A1,FEB!A1,MAR!A1))
(2) =ARRAYFORMULA(VLOOKUP(A1:A3,VLOOKUP!A1:B3,2,0))
(3) =QUERY(A1:B3, "SELECT A, SUM(B) GROUP BY A LABEL SUM(B) 'Totals'")
``````

## Screenshots

(1) (2) (3) ## Explained

The `ARRAY_ROW` formula allows for easy preparation of a range. The `TRANSPOSE` formula re-arranges the row into a column position. This way all entries are nicely presented (1). The `VLOOKUP` and the `ARRAYFORMULA` will efficiently look for all corresponding numbers in the VLOOKUP sheet (2). The `QUERY` formula is used to perform the totals calculation (3).

## Example

I've created an example file for you: Google Sheets advanced lookup + sum

Assuming your sheets are numbered 1 through 12, the lookup value is in ColumnA and the return value in ColumnB of each sheet and the selected value is in A1:

``````=vlookup(A1,'1'!A:B,2,0)+vlookup(A1,'2'!A:B,2,0)+vlookup(A1,'3'!A:B,2,0)+vlookup(A1,'4'!A:B,2,0)+vlookup(A1,'5'!A:B,2,0)+vlookup(A1,'6'!A:B,2,0)+vlookup(A1,'7'!A:B,2,0)+vlookup(A1,'8'!A:B,2,0)+vlookup(A1,'9'!A:B,2,0)+vlookup(A1,'10'!A:B,2,0)+vlookup(A1,'11'!A:B,2,0)+vlookup(A1,'12'!A:B,2,0)
``````

Assumes also that all sheets have such an instance, otherwise each lookup should be wrapped in an IFERROR with `0` as the output for any error.