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

migrated from superuser.com Sep 16 '14 at 14:54

This question came from our site for computer enthusiasts and power users.

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

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.

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.