0

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:

https://docs.google.com/spreadsheets/d/119bdGbcpM2HnyQinESahOg-jtPJ-NvjUX9Cl1HtB_l0/edit?usp=sharing

2

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)
enter image description here

(2)
enter image description here

(3)
enter image description here

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

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.