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.

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:

=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:

• Referring to  a range of cells in another worksheet
• How to combine values from a range of cells

How to refer to a range in another sheet

If you use multiple worksheets in a spreadsheet, then you can refer to an individual cell in a worksheet - including one that you are not currently working in - like this:

=Sheet2!B1 

(A formula like this returns the value in Worksheet Sheet2, Cell B1.)

And you can even combine values from different sheets by putting different sheet-name references into a formula like this;

=Sheet2!B1 + Sheet3!B1

(A formula like this returns the value in Worksheet Sheet2, Cell B1 plus the value in Worksheet Sheet3, Cell B1)

This makes some people think that they should refer to a range of cells in a different worksheet (eg B1:b16) like this:

=SUM(Sheet2!B1:Sheet2!B16)

However using this formula shows #ERROR! as a result, with a not-very-helpful error message of "Formula Parse Error".

The correct way to refer to a range of cells in a separate worksheet is to use the sheet-name only once, like this:

=SUM(Sheet2!B1:B16)
(A formula like this returns the sum of values in cells B1:B16 in Sheet2.)

The reason for this is that within the one function call (eg SUM(...) or AVERAGE(...) ) all the cells must come from the same worksheet. It does not make any sense to say:

=SUM(Sheet2!B1:Sheet3!B16)

because it's not defined what is in-between Sheet2 and Sheet3.

But it does make sense to combine function calls like this:

=SUM(Sheet2!B1:B16) + =SUM(Sheet3!B1:B37) 

(A formula like this returns the sum of values in cells B1:B16 in Sheet2, plus values in cells B1:B37 in Sheet3)

Extra for Experts

Of course this only works if they type of function that you are using is distributitive, ie you can do one part and the other part, and then you put them both together using the same functon, eg

= MIN ( MIN(Sheet2!B1:B16), MIN(Sheet3!B1:B22))
(A formula like this returns the smallest value in cells B1:B16 in Sheett and cells B1:B37 in Sheet3)

For functions like AVERAGE, where the number of items in the underlying range is used in the calculation, it does not work, eg

= AVERAGE ( AVERAGE(Sheet2!B1:B16), AVERAGE(Sheet3!B1:B22)) 

will give a result but it will generally not be the arithmetic mean of the value in cells B1:B16 in Sheett and cells B1:B37 in Sheet3 because the values from Sheet2 will get too great a weighting in the calculation.

Was this helpful?  You might also like: 

• Understanding spreadsheets, sheets and worksheets
• Where to get help with Google Sheets

How to find the difference between two time values

Calculating the difference between two times (or date-times) in Google Spreadsheets is just as simple as calculating the difference between two dates, provided the time values are formatted as Times.

You simply do a "minus" between the later time and the earlier one.

At its simplest, you just do a minus calculation, like this:

C5 = C4 - C3

The result that is shows is the time differences, expressed as the number of days between the two values.

You may want to do further calculations, or apply Duration formatting, to make this number more user-friendly.

Troubleshooting

If it looks like your time difference calculation is not working correctly, then then are are few things to check.

• Are your time values (ie not just the result) formatted as Times?
  Check this under menu: Format > Number > Time.  
  
  NB   It is not the same as the Font of "Times", which is totally different

• Do you know that what is shown is the number of days, and this may be a fraction or decimal value. 

Possibly it is not  formatted the way you want it to be. 

Check the formatting under Format > Number, and remember that 15 minutes = 0.25 of an hour, or 0.010416667 of a day.

Extra for experts

A simple minus calculation only works correctly if:

• Both times are in the same time-zone
• If the later time is on a different day, then the values that you are calculating on must both have a "date" part as well as a "time" part.

If you need to cover these cases, then check back again soon, so see posts explaining how to handle these situations.

Was this helpful? You may also need to read

Calculating the difference between two dates
Display formats and date / time differences

How to show the difference between two dates

Finding out how long between two dates in a Google Spreadsheet is very simple: you just subtract the later one from the earlier one, like this:

Looking in more detail at what happens on the spreadsheet in this formula, you just need to

• Type an equals sign ("=") - this tells the spreadsheet that you are going to put a formula into that cell
• Type (or point to) the later value
• Type a minus sign ("-")
• Type (or point to) the earlier value
• Press Enter

Google Sheets uses colour-coding in the cells and dotted lines to help you see exactly what cells the formula is pointing to.

Was this helpful?   You may also need to read:

• Date-calculations and Plus-One: whole vs partial days
• Calculating the difference between two times
• Google Sheets date-format effects on calculation results

How to combine a range of values into one cell, with a character in between them

If you have a list of items in a spreadsheet, set up like a table, then you can use the join function to combine the values and display them all together in a horizontal list, with some characters (eg a comma and a space) in between tehm..

For example, in my summer-school management spreadsheet I have a list of teachers in column C, currently in rows 1 (heading) to five.

 Teachers
-----------
 Fiona
 Patrick
 Sean
 Lynne


To put the teacher-names into the one cell, nicely formatted with a comma and a space between each one, I could use the JOIN() function, like this:

= JOIN( ", " ; C2:C5 )

But there are two problems:

• The result is "Fiona, Patrick, Sean, Lynne, " - there is an extra "comma space" at the end

• If the number of teachers changes, the I have to adjust the formula

A better option it to also use the FILTER() function, to remove values that you dont't want in this case, blanks which are represented by "".

So the formula becomes:

 = JOIN( ", " ; FILTER(C2:C9999; NOT(C2:C999 = "") )) 

This says to join all the values in cells C2 through to C9999 together, to put a comma and space between each one, but to leave out any that are blank,

Using this, my list becomes "Fiona, Patrick, Sean, Lynne", and I can add up to 9998 names in the Teachers column and it still works.

Extra for Experts

If I had used named ranges, and so didn't have a heading at the top of the column, then the formula could become even more flexible, with no limit to the number of rows included, like this:

= JOIN( ", " ; FILTER(C:C; NOT(C:C = "") ))
or

= JOIN( ", " ; FILTER(Teachers; NOT(Teachers = "") ))