How To Add A Number To A Cell If A Different One Contains A Letter Google Sheets

The Google Sheets Query function replaces so many other spreadsheet functions it's not even funny – FILTERs, AVERAGEs, and SUMs all exit the window when it enters the picture.

In that location are a few nuances to queries though, so let'southward go footstep-by-footstep. This mail service contains eight videos, with cliff'south notes embedded beneath each:

Let's dive in!

Btw…if yous're looking to make the spring from Sheets to BigQuery, bank check out our BigQuery quickstart Recipes over at query.recipes.

1. The basics of Google Sheets Queries

QUERY combines all of the capabilities of arithmetic functions (SUM, COUNT, AVERAGE) with the filtering abilities of a function similar FILTER.

In this example, we'll walk through a few QUERY examples, to analyze a sampling of Twitter information.

The bones construction of a QUERY is to input:

A information range – 'information tab'!A:C'
And a query cord – "select A where C = 'twitter'"

We combine them similar so:

=query( 'data tab'!A:C, "select A where C = 'twitter'")

Example QUERY functions to answer questions

Q: What was the boilerplate number of retweets from the sample?

A: =query(D1:G, "select avg(G)")

Q: What was the average number of retweets on Tweets that contained an @mention only weren't a retweet?

A: =query(D1:Thousand, "select avg(G) where E contains '@' and not East contains 'RT'")

Q: How many retweets did each user in the listing receive?

A: =query(D1:1000, "select F, sum(G) where Due east != '' group past F label sum(G) 'total retweets'")

2. Writing date comparisons in queries

Why so complex in Sheets queries – shouldn't this be easier?

When I first started writing date comparisons within Google queries (pulling data before / later on a sure date), information technology was a pain to figure out the correct syntax.

alt text

Unfortunately it'south not really mentioned anywhere in the Google Sheets documentation, so I had to figure it out for myself.

You have to do two things correctly:

Write the cord 'appointment' earlier the actual engagement, to declare your intentions
Type your date in the format 'yyyy-mm-dd'

If y'all want to compare a date dynamically from some other prison cell, it'southward a chip of a different story. I like to keep engagement ranges (last vii days, terminal thirty days, yr-to-date, etc), then reference them in a query similar this:

alt text

Notice the syntax:

To combine the string with an exterior value, close the query double-quotes, and use '&'
Use the TEXT function to catechumen the date into the correct 'yyyy-mm-dd' format

Once yous get the hang of it, you'll never retrieve about it again. Just if you do ever forget the syntax, the CIFL crook canvass has your dorsum.

3. Debugging common Query errors

On debugging common Query errors:

Errors are never fun, but Sheets' fault letters volition always bespeak yous in the right direction.

#REF

i) Assortment event was non expanded because it would overwrite data in D6

There's data in cell D6 blocking your query from expanding – remove it!

ii) Unresolved canvass name…

The sheet that you lot're referencing in your query doesn't exist – probably due to a typo or having deleted information technology :(.

#VALUE

iii) PARSE_ERROR

Caused by a misinformed query cord – it volition always tell y'all exactly where to wait:

alt text

Would mean there's a syntax error somewhere environment my 'where' clause – a hanging comma or a typo:

=query(tweets!A1:D,'select C, sum(D), where D >= 0 grouping by C limit 2',0)

4) LABEL_COL_NOT_IN_SELECT

Caused by a mismatch betwixt columns in your 'select' clause and your 'label' clause:

=query(tweets!A1:D,'select C, sum(D) where D >= 0 group by C limit ii label sum© ",0)

Here sum(D) is mislabeled as sum©.

5) NO_COLUMN

When you're selecting a column that's outside of your range:

=query(tweets!A1:C,'select C, sum(D) where D >= 0 group by C limit 2',0)

Remedied by expanding your data range to include the column (tweets!A1:D in this case).

6) ADD_COL_TO_GROUP_BY_OR_AGG

Pops up when you're selecting a column (B in this case) that isn't present in your 'group by' clause:

=query(tweets!A1:D,'select C, B, sum(D) where D >= 0 grouping by C limit 2',0)

If you're not summing or accumulation column B, then y'all have to group by it.

#N/A

7) Query completed with an empty output.

But means in that location's no results from your query as currently written – potentially due to a too-restrictive status:

=query(tweets!A2:D,'select C, D where D = 100 limit ii',0)

Try simplifying your query to be less restrictive (removing the D = 100 in this example).

four. How to Query multiple Sheets / ranges at the same time

On querying multiple Google Sheets, with importrange + {}:

There comes a time in every life, where you want to combine two data ranges within a Google Sheets query.

Let's talk about how to combine data ranges from within the aforementioned spreadsheet (or from 2 unlike sheets), to run ane query – working through examples using sample Twitter data.

To query a combined range from within the same sheet:

alt text

Enclose both ranges inside { } to combine them, and separate with a ';' to stack one on top of the other
Instead of referencing columns with their letter (A, B), refer to them as Col1, Col2 depending on their club

To query a combined range from different sheets:

First run the importrange function on the outside Sheet you'd like to pull from. The commencement chemical element is the spreadsheet ID, which is contained within the URL of the sheet, betwixt /d/ and /edit: (docs.google.com/spreadsheets/d/
1ekTIZ40VVT9p9kE80wNaiy6Y2xslo4K9KVmmg-Dx654 /edit).

alt text

Y'all'll be asked to allow access to the sheet.

alt text

Embed the range from your importrange role within the query itself.

alt text

To be honest, my goal is to never have to combine ranges from within a query itself (I prefer to do that type of assemblage earlier writing a query), but sometimes it's unavoidable.

5. Query by column names not letters

This one volition save you major headaches if multiple colleagues work in your Sheets.

Queries are sensitive creatures – they require us to specifyexactly the column letters ( A, sum(B) ) or numbers ( Col1, sum(Col2) ) that nosotros want to return.

Just given how we use Sheets, those columns may be changing all the fourth dimension. People add new rows, delete rows, and generally shift things around.

By nesting ii queries together, we tin future-proof your queries to e'er match the aforementioned column headers:

The first (inside) Query uses the Match function to lookup your column numbers – lucifer('header name', tab!:A1:Z1), assuming your headers are in the first row. Note beneath that nosotros'll desire to wrap our range { tweets!A2:D } in curly braces, to gratuitous usa upwards to select cavalcade numbers (Col1) rather than letters (A).
The second (outside) Query is where y'all build your bodily select logic (select Col1, sum(Col2) grouping by Col1…), considering in the inside Query yous've already plucked out the specific columns you demand. Yous tin hardcode in Col1, Col2, etc, because you always know the gild in which they'll appear, regardless of whether the columns motion around in your bodily Sheet.

Put it all together, and it looks like this:

alt text

The showtime line (inside query) finds your column names – the actual query its running is just 'select Col3, Col4.'

The 2nd line (outside query) runs your bodily math and labels your columns. Retrieve that query column labels will always appear at the finish of the statement, and look generally like the SELECT statement (select Col1, sum(Col2) maps out to label Col1 'handle', sum(Col2) 'full retweets').

This can accept a fleck of playing around to go comfortable with – feel free to re-create the demo Sheet used in the video to re-use our formula.

6. Building Pivot Tables with Query

Slap-up for building fourth dimension serial reporting in Sheets:

If you're working with time-series data in Sheets (like most of u.s. do), and so pivoting in queries volition exist your new favorite motion.

It lets y'all build full report in one Query, by group values down the left column, and transposing engagement ranges (or any 2nd column) to the right.

alt text

You pin with a very similar syntax to grouping – this query produces the outcome above:

=query(tweets!A1:D,'select C, sum(D) where D >= 0 group by C pin A',0)

As you can encounter, it's as uncomplicated as calculation 'pin A' later the 'grouping by' clause in a query.

You tin use dropdowns to add input variables within your query:

When you're building a report in Google Sheets, a solid goal is to not make the people *using* the report think.

Dropdown menus are dandy for this, since they permit you to present a limited number of options to report users.

Data validations in Sheets automatically create dropdown menus when you create them – pretty slick right?

And blending the selected value from your dropdown menu with a Query is unproblematic:

=query(data!A:B, 'select A where B = " & 'dropdown tab location'!C2 & "', 0)

It but takes a few keystrokes:

Wrap your dropdown value in single quotes ('), if it's a string (none if it's a number)
Shut (and and then re-open) your query with double quotes (")
Use ampersands (&) to add the dropdown value to the query cord
Reference the bodily cell of the dropdown value ('data'!A2) to add it to the query cord

That's it! Your report users will thank you.

eight. The difference between SQL and Query

What's the difference between the Sheets Query and SQL?

If yous're used to writing SQL queries, it tin can be off-putting at first to piece of work with Google Sheets queries (and vice-versa).

If you don't know any SQL yet, acquire Query beginning. It'll make working with databases *much* easier when you become there.

Let's walk through a number of ways in which they're similar & different, and how you tin can adapt each of them in your piece of work.

Basic querying

SQL: SELECT * FROM table WHERE column_name = 'xyz' ORDER BY column_name desc LIMIT 10

Sheets: =query( 'tab'!A:D, 'SELECT * WHERE A = 'xyz' Club BY A desc LIMIT 10')

The basic query syntax is roughly the same. Google Sheets queries use the aforementioned SELECT statement to choose columns, WHERE / AND / OR to ready logic, Society By to accommodate results, and LIMIT to pull but a sure number of results (see a total listing of language clauses in the Googledocs).

The key departure is that there's no 'FROM' statement in a Sheets query – instead of referencing a table, you reference a range of cells ('tab'!A:D in this example).

As well, yous'll detect that, instead of referencing column names (similar in SQL), in Sheets queries yous reference columns by their letter of the alphabet (A, B, C, etc) or by their order (Col1, Col2, Col3).

Doing math on columns

SQL: SELECT column_name, sum(other_column_name) FROM table GROUP BY column_name

Sheets: =query( 'tab'!A:D, 'SELECT A, sum(B) GROUP Past A')

The same avg(), count(), sum(), max(), min() aggregations functions that y'all're used to using in SQL are available in Sheets queries, as are bones +, -, *, / arithmetic functions.

Labeling columns

SQL: SELECT column_name AS 'characterization' FROM table

Sheets: =query( 'tab'!A:D, 'SELECT A, B LABEL A 'label1', B 'label2' ')

In SQL, to characterization a column y'all simply add an 'Every bit' to your 'SELECT' statement: column_name AS "label."

In Sheets, this is done at the end of a query, with the 'characterization' statement.

Labeling the sum(A) cavalcade as blank removes the automated sum() header from being displayed, returning a nice clean unmarried metric.

Date comparisons

SQL: SELECT column_name FROM table WHERE date_column > '8/22/2016'

Sheets: =query( 'tab'!A:D, 'SELECT A WHERE C > engagement '2016-08-22' ')

Dates in Google queries are tricky to figure out the beginning fourth dimension, if you're used to working in SQL.

Unlike SQL, which can handle multiple date formats ('20160826', 'eight/26/2016', '2016-08-26'), Sheets tin can only take dates in format 'yyyy-mm-dd' – and then you sometimes accept to use the TEXT function to transform a appointment similar 8/26/2016 into 2016-08-26.

In Sheets, you lot also have to specify that you lot're comparing a date, past calculation the phrase 'date' earlier your date cord.

Fuzzy phrase matching

SQL: SELECT column_name FROM table WHERE column_name LIKE '%phrase%'

Sheets: =query( 'tab'!A:D, 'SELECT A WHERE A CONTAINS 'value' ')

To pull text that contains a value, yous tin simply use 'contains,' versus the 'similar' statement in SQL. Sheets queries do accept a 'like' statement, only I find myself using 'contains' much more frequently considering of its simplicity.

Ane added contraction though, if you lot're looking to add a 'non contains' parameter, you have to place the 'non' before the column letter of the alphabet.

Joining tables

SQL: SELECT a.column_name, b.column_name FROM table1 a, table2 b WHERE a.column_name = b.column_name

Sheets: Not supported ????

My biggest pet peeve with Sheets queries is that they don't back up joining two tables together past a unique cardinal. I've tried every workaround I can remember of, and and so far oasis't found a style.

Google Sheets squad, if y'all're reading this – help!

Match from a list of options

SQL: SELECT column_name FROM tabular array WHERE column_name IN ('match1', 'match2', 'match3')

Sheets: =query( 'tab'!A:D, 'SELECT A WHERE A = 'match1' OR A = 'match2' OR A = 'match3' ')

Unfortunately in Google Sheets, at that place's no such thing every bit the 'IN' phrase in SQL, and then you accept to list out values y'all're looking to match for one-by-one.

9. Nesting Queries together like SQL

With a flake of a hacky approach, you tin approximate SQL's nesting office in Sheets:

As I mentioned higher up, not beingness able to join queries in Sheets is a major drawback.

Say yous have information in ii dissimilar tabs, and you want to pull all of the values from i table that exist in the other.

I idea it wasn't possible. I was wrong.

It'southward a hacky manner of imitating the style that SQL queries can nest, but it works.

You combine arrayformula and concatenate, to produce a long string within your query ( OR A = 'value1' OR A = 'value2', etc).

The final formula looks similar this:

=query(tab1!A:Z,'select A,sum(B) where A != ' and (B = 'blah' ' & concatenate(arrayformula(' OR B = " & tab2!C2:C & "')) & ') group by B',0)

This takes all of the values that are nowadays in Tab 2, cavalcade C, and looks for them in cavalcade B of Tab i.

It's a poor man's nesting query, but it works :).

Need help?

That was a lot of Google Sheets querying, but I hope it's starting to sink in for you.

Brand sure to grab a free copy of the Sheets formula cheat sheet hither – only if you've made it this far, you're probably ready to master Sheets by investing a few hours in the advanced CIFL Querying course (which includes detailed walkthroughs and quizzes not plant hither).

Or, hitting u.s. up directly – our team of consultants is standing past to help yous solve your almost complex QUERY problems.