Google-sheets – Advanced lookup + sum

google sheets

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

Best Answer

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

Related Solutions

Google Sheets Count – Formula to Count All Cells Containing Specific Texts in Google Sheets

You can filted by regexmatch instead of equality.

=countunique(filter(Sample!C2:C, regexmatch(Sample!A2:A, "Nov")))

Matching the regular expression "Nov" simply means containing "Nov" somewhere in the string.

Google-sheets – Google sheets, scatter plot, multiple datasets, with datapoint labels

If by "add data labels for each data point" you mean "adding labels from A column to each individual point" - then that is not possible. you can add only values as a label for each data point.

As for "two independent axis" - this can be easily achieved by adding new series from another column. All the above would look like:

From there you can add trendlines or customize each individual data point by changing color, size and shape:

If you will re-shape your table a bit you could do this:

Of course, if you really want so you could add custom label per each data point manually by inserting a drawing: