Functions in google sheets are a great way to do complex calculations. Single functions in a cell are helpful for simple calculations but for complex calculations involving logical conditions, you need to combine multiple functions. Also useful when you just want to print the output from multiple functions in one cell for better data visualisation.
In this tutorial, we will learn how google sheets multiple functions in one cell work.
Google sheets multiple functions in one cell steps
Here is how to:
1. Select the cell where you want to write multiple formulas
3. Write the first formula and add a & sign at the end
4. Now add a pair of double quotes with a space between them
5. Then add another & symbol and write the second formula
6. Press enter and the output will have both formulas separated by a space
The binary concatenate operator (&) combines multiple functions in one cell in google sheets.
Let’s say you want to use these steps to write the SUM() and AVERAGE() formula in one cell.
Here is a quick animation showing google sheets multiple functions in one cell:
The need of having multiple functions in one cell depends on the type of problem you are solving. In this guide, I will explain all these scenarios with examples for easy understanding.
Table of content:
Google sheets multiple functions in one cell using the “&” operator
Let’s say we have the below data from a customer lead form.
Example: 5 formulas in 1 cell
The owner wants to know how many people are looking for Sneakers and who they are.
You can answer these questions by using individual formulas in separate cells.
You can have one COUNTIF formula for counting the total number of people and one QUERY formula for showing their name and another QUERY formula to show their age.
Or you can choose to combine all formulas in one cell.
Check the below result:
Here is the formula:
="The following "&COUNTIF($A$2:$A$10, "Sneakers")&" People would like to purchase Sneakers:"&char(10)&arrayformula(join(char(10),(QUERY(A2:C, "Select B where A='Sneakers'")&", Age "&QUERY(A2:C, "Select C where A='Sneakers'"))))
All formulas are joined by using the & character between them.
The COUNTIF formula will count the number of times the word “Sneakers” appears in the cell range A2:A10.
char(10) will add a new line in the result.
join formula will add the comma to the result
The first QUERY formula will select cells from column B where the cell in column A has “Sneakers” in it. This will output the name of the customer.
The second QUERY formula will select cells from C where the cell in column A has “Sneakers” in it. This will output the age of the customer.
Finally, the ArrayFormula will ensure the formula is applied to all cells in the range and output the result in one cell.
This is how you combine multiple formulas in one cell in google sheets.
Google sheets multiple functions in one cell using concatenate function
You can also combine multiple functions in one cell using the CONCATENATE function. This works the same as the binary concatenate operator.
Here is an example showing this in action.
Find Mean, Median, and Standard deviation in one cell
Let’s say we have a list of numbers in column A. We want to know their Mean, Median, and Standard deviation. All in one cell.
So we have below 3 formulas that we are combining:
And here is the formula
=CONCATENATE("Mean: ",AVERAGE(A1:A5),CHAR(10),"Median: ", MEDIAN(A1:A5),CHAR(10),"SD: ",ROUND(STDEV(A1:A5),3))
Note: CHAR(10) will add line breaks and ROUND() will round up to 3 decimal.
Here is the data set and the result:
As you see here, you have all 3 formulas in one cell.
Google sheets multiple functions in one cell using Logical expressions
Let’s say you have multiple conditions to evaluate and on successful evaluation one function will execute.
For example, if the average of all numbers is less than 10 then multiply each number by 5.
Another example is, to go through a list of numbers and if it is odd, then convert it to an even number.
Here multiple functions will execute in a series to result in an output.
Let’s demo these examples in google sheets.
In the below example, you have a list of numbers in cell range A1:A10. If the average of the range of numbers is less than 10 then you will multiply each number by 5
The formula is
Here is the result:
First, we will write the IF formula which takes 2 parameters. First is the logical condition. If it’s TRUE then the second parameter will execute.
AVERAGE(A1:A10)<10 – checks if the average is less than 10
MULTIPLY(A1:A10,5) – multiply cell value by 5
Finally the Arrayformula will apply the function in all cells.
In the same data set let’s check if any number is odd then make it even.
The formula is:
Here is the result:
ISODD() function checks if the value is odd. If yes, then the IF statement will execute the second parameter to add +1 to the number.
The array formula will apply the function in all the cells in the range. Wherever the number is even, the IF statement will fail and the output will be FALSE.
You can write a custom message instead of printing False like this:
This will output “Even number” instead of “FALSE”.
So this is how you combine multiple functions in one cell using logical statements.
You can also use nested if or IFS statements to combine multiple conditions. And each condition can execute one function.
So this is how we approach Google sheets multiple functions in one cell problems.
How do I combine two formulas in google sheets?
To combine two formulas in one cell, use the concatenate function or the binary concatenate operator.
How to put multiple functions in one cell in google sheets?
You can use the same concatenate method to put more than two formulas in a cell in google sheets.
Also, you can use logical statements to combine multiple functions if they are related to calculating the result.
In this guide, we learned about the concept of google sheets multiple functions in one cell. Combining multiple formulas is complex but many times you need them to solve specific problems.
I am confident that with this learning you will be able to write multiple formulas in one cell in google sheets. So go ahead and practice this in your next assignment.
 CONCATENATE formula in google sheets – Link
New to google sheets ? Start here