Adding values in one column, based on another one, using the SUMIF function
This post explains how to calculate the total of the values in one row or column, based on the corresponding values in another related row or column, using the SUMIF function in Google Sheets.
New tool for using Google Sheets on your smartphone or tablet
Google have announced new apps for several components of their Drive software, including Google Sheets.
They say this makes it possible to create content on the go - but even with a very new smartphone, I'm finding it hard to imagine making a spreadsheet of any size. It just feels weird to have the formula-bar at the bottom of the screen.
And I've had some "interesting" experiences browsing a spreadsheet created in the desktop version which records the responses to a questionnaire created in Google Forms, which has a lot of long text-fields: it's easy to hide a column by accident, and without a shift-key I'm not sure how to make visible again.
But overall I like the idea, especially the way that I can effectively use Sheets as a calculator app which lets me see and easily change my on-the-go calculations. And I can imagine that it could be a handy way to build some highly targeted "mini-apps" to do particular calculations in a spreadsheet, without the overhead of installing a separate app.
 |
| Screenshot of the new Google Sheets app for Android |
They say this makes it possible to create content on the go - but even with a very new smartphone, I'm finding it hard to imagine making a spreadsheet of any size. It just feels weird to have the formula-bar at the bottom of the screen.
And I've had some "interesting" experiences browsing a spreadsheet created in the desktop version which records the responses to a questionnaire created in Google Forms, which has a lot of long text-fields: it's easy to hide a column by accident, and without a shift-key I'm not sure how to make visible again.
But overall I like the idea, especially the way that I can effectively use Sheets as a calculator app which lets me see and easily change my on-the-go calculations. And I can imagine that it could be a handy way to build some highly targeted "mini-apps" to do particular calculations in a spreadsheet, without the overhead of installing a separate app.
The Sheets app vs the Drive app
This comment from Google sums up the difference - bolding mine:use the Drive app to view and organize all of your documents, spreadsheets, presentations, photos and more.And by implication, use the Sheets, (and Docs, and when it arrives Slides) app to change (ie edit) your spreadsheets et al.
How to combine a cell value and a text-string
Sometimes you may want to write a formula which combines the value(s) from some cells with some other text, and puts the result into the one cell.
For example, I have a spreadsheet which generates the HTML code statements for drawing a table, based on the values which I put into a table-area on the spreadsheet. And I need to combine the values from the table area with other HTML commands like "" and ""
The way to do this in Google Spreadsheets is to use a string concatenation operator (&) or a string-concatenation function ( CONCAT( , ) or CONCATENATE(...) )
For example, I label the value from C5 with the text "Score type:", and so in the results column which I use to make the table-cell code for it, the formula is:
Or a less verbose way to achieve the same thing is:
The CONCATENATE() can be used to join any number of text strings together, so another way to write the statement is
In general, it's best to use the option which makes the formula the most readable, or easiest to diagnose if something goes wrong.
So often I will use the CONCAT() function in each one of a set of helper columns, and then use one CONCATENATE() function at the end to join them all together, because this is easier to debug than trying to do a lot of string operations all in the one cell.
For example:
For example, I have a spreadsheet which generates the HTML code statements for drawing a table, based on the values which I put into a table-area on the spreadsheet. And I need to combine the values from the table area with other HTML commands like "" and ""
The way to do this in Google Spreadsheets is to use a string concatenation operator (&) or a string-concatenation function ( CONCAT( , ) or CONCATENATE(...) )
For example, I label the value from C5 with the text "Score type:", and so in the results column which I use to make the table-cell code for it, the formula is:
=CONCAT("<td>Score type: ", CONCAT(C5, "</td>"))
Or a less verbose way to achieve the same thing is:
="<td>Score type: " & C5 & "</td>"
What's the difference between "&" CONCAT and CONCATENATE?
The CONCAT() function and the "&" operator do the same thing, so whether you use one or the other is is about which one looks nicer to you.The CONCATENATE() can be used to join any number of text strings together, so another way to write the statement is
=CONCAT("<td>Score type: ", C5, "</td>"))
In general, it's best to use the option which makes the formula the most readable, or easiest to diagnose if something goes wrong.
So often I will use the CONCAT() function in each one of a set of helper columns, and then use one CONCATENATE() function at the end to join them all together, because this is easier to debug than trying to do a lot of string operations all in the one cell.
For example:
Was this helpful? You might also like:
Subscribe to:
Posts (Atom)