Google Sheets – Compile Cells Based on Certain Value

formulasgoogle sheetsgoogle-sheets-query

In Google Sheets, I have a range of values as follows:

I am looking for a formula that will search the range, find all of the cells that is a 'wire' (contains the term "wire") and outputs the range to a compiled list of all of the wires in the A column list like this:

I could tell you what I've tried, but I haven't even gotten close, so I feel it's irrelevant.

Here is a copy of the example spreadsheet for copy-paste if needed: https://docs.google.com/spreadsheets/d/1QDvAo5epuwBEzeOPrBckb1GO6wKOA6q6K83C1-SeQGA/edit?usp=sharing

Best Answer

=QUERY(A:A; "where A contains 'Wire'")

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

Best Answer

Related Solutions

Google Sheets – Copy Lower Triangle to Upper Triangle

Google-sheets – Advanced lookup + sum

Formulae

Screenshots

Explained

Example

Related Topic