Tag: formulas

Formula Challenge #6: Split A String Into Characters And Recombine In Random Order

This Formula Challenge originally appeared as Tip #194 of my weekly Google Sheets Tips newsletter, on 7 March 2022.

Congratulations to everyone who took part and well done to the 97 people who submitted a solution!

Special mention to Kieran D., Louise A., Martin H., Jelle G., Karl S., Earl N., Doug S., JP C., Alan B., and others for their ingenious solutions. I’ve shared the best below.

Sign up here so you don’t miss out on future Formula Challenges:

 

Find all the Formula Challenges archived here.

The Challenge Part I: Split A String Into Characters

Question: can you create a single formula to split a string into characters so that each character is in its own cell?

i.e. can you create a single formula in cell B1 that creates the output shown in this example:

Formula Challenge Split String Into Characters

Continue reading Formula Challenge #6: Split A String Into Characters And Recombine In Random Order

How To Use The RANDARRAY Function In Google Sheets

The RANDARRAY function in Google Sheets generates an array of random numbers between 0 and 1. The size of the array output is determined by the row and column arguments.

Here’s a RANDARRAY formula that generates an array of random numbers between 0 and 1, across 10 rows and 3 columns:

=RANDARRAY(10, 3)

which gives an output:

RANDARRAY Function Example

🔗 The RANDARRAY template is available at the bottom of this article.

Continue reading How To Use The RANDARRAY Function In Google Sheets

Google Sheets SUMIFS Function

The Google Sheets SUMIFS function is used to sum ranges based on conditional tests. In other words, the SUMIFS in Google Sheets adds values to a total only when multiple conditions are met.

Suppose we want to calculate the total for Large products in New York:

Simple SUMIFS Google Sheets function

The SUMIFS function to calculate the total for two conditions, Large and New York, is:

=SUMIFS(D2:D11,B2:B11,"New York",C2:C11,"Large")

which gives an answer of $3,020.

In this case, there are four rows, highlighted in yellow, that match the criteria of New York in column B and Large in column C.

The total values of these four rows are added together by the SUMIFS function, all the other rows are discarded.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading Google Sheets SUMIFS Function

How To Use Google Sheets SUMIF Function

The SUMIF function in Google Sheets is used to sum across a range of cells based on a conditional test. The SUMIF function only adds values to the total when the condition is met.

Let’s see an example. Suppose we want to calculate the total order value for John only:

Google Sheets SUMIF Function

The SUMIF formula that calculates the total order value for John is:

=SUMIF(A2:A11,"John",C2:C11)

which gives an answer of $2,059.

The formula tests column A for the value “John”, and, if it matches John, adds the value from column C to the total. I’ve highlighted the four rows in yellow that are included.

🔗 Get this example and others in the template at the bottom of this article.

Continue reading How To Use Google Sheets SUMIF Function

VLOOKUP Function in Google Sheets: The Essential Guide

The VLOOKUP function in Google Sheets is a vertical lookup function. You use it to search for an item in a column and return data from that row if a match is found.

In the following example, we use a VLOOKUP formula to search for “Charles Dickens” in column 1. When we find it, the formula returns the value from the 4th column of the lookup table to give a result of $299.

Google Sheets VLOOKUP Function Explained

In this example, the VLOOKUP function is:

=VLOOKUP(B8,A2:D5,4,false)

Let’s break this formula down:

B8 is the search term: “Charles Dickens”

The VLOOKUP looks down the first column of the lookup table: A2:D5

If it finds the search term, it then looks across that row to the column indicated by the Index number: 4

It then returns the value from column 4 as the answer, which is $299 in this example.

The final argument is false, meaning this is an exact match.

VLOOKUP can also handle approximate matching as well as wildcard searches. These more advanced use cases are explored further below.

Continue reading VLOOKUP Function in Google Sheets: The Essential Guide