Google Sheets has come a long way in simplifying and making work easier for professionals in different fields. This applies to marketers too, as it allows collaboration between team members and also keeps a track of changes made. Because of its various features and offerings, it has become a very popular tool that everyone uses, from amateurs to professionals, from small businesses to large enterprises.
So, here are some useful formulas and functions in Google Sheets that you can use to make your work more efficient.
Many conditional formulas act only if it matches a particular condition. These formulas include SUMIF, COUNTIF, and IF to name a few.
The SUMIF function finds the sum of a particular range of numbers only if they meet particular criteria as defined by the user.
The generic formula of SUMIF is
=SUMIF(range, criterion, [sum_range])
Where the range is the range of cells to be evaluated, the criterion is the condition that has to be met and sum_range is the range of numbers that will be summed up. The sum_range argument is optional, and if not specified, the range will be summed up.
For example, to calculate the total price of apples from a list of fruits, we can use the following formula:
=SUMIF( A2:A11, “apple”, B2:B11)
The COUNTIF function works in a similar manner to SUMIF, but it only counts the number of instances of data meeting a particular criterion.
The formula for the COUNTIF function is
Where range refers to the range of cells to be tested and criterion is the test or condition to apply to the range.
Suppose you have a list of employees with their sales figures as shown in the table and you want to find the number of employees with sales over $3000, then you can use the COUNTIF formula.
The IF function is used to check for the validity of particular conditions for the elements of a cell. It always has a TRUE or FALSE value and the output takes place in accordance with the value the syntax is as follows.
=IF(logical_expression, value_if_true, value_if_false)
Here, logical_expression refers to the question or logical argument that is to be tested. It involves operators like “=”, “>”, “<“, “>=”, “<=”, “<>”. The value_if_true and value_if_false, arguments specify output when the logic is true and when it is false.
There are many formulas in Google Sheets that are specifically used for textual or string data. This includes LEFT, RIGHT, LEN, TRIM, and SPLIT. Let us take a look at each of these formulas.
LEFT allows users to cut off segments of text from the left side of the cell while the RIGHT function lets you cut off segments of text from the right side of the cell.
The syntax given is:
=LEFT(cell address, number of characters)
Here, cell address refers to the address of the cell containing the text. You can also replace the cell address with the actual text and enclose the string data within double quotes. The second argument refers to the number of characters from where you cut off the text data.
=RIGHT (cell address, number of characters)
The LEN formula is used to find the number of characters in a string. It includes spaces, punctuation, and also special characters.
The syntax is very simple and the argument specifies the cell address of the text.
= LEN(cell address)
Shown in the example below is the length of characters of a Shakespeare quote.
The TRIM formula removes spaces from either side of a text or string.
The syntax is
The cell address refers to the address of the cell containing the text.
The SPLIT function divides a given string in a cell based on a specified delimiter and places the divided elements of the string in separate cells.
The formula is as under.
= SPLIT (text, delimiter, [split_by_each], [remove_empty_text])
- cell address refers to the address of the cell containing the text
- delimiter specifies the point of the split of the text
- split_by_cell value is True by default and it divides text around each character contained in the delimiter
- remove_empty_text also has a True value by default and it removes empty text messages from the split results.
Note: You have to mention False explicitly if you want to change their default values. The delimiter has to be mentioned within double quotes.
The example below will help you understand its usage better.
Here we have a list of names. We can use the SPLIT function to separate the first name and the last name.
We can then drag down the cell that has the formula to apply it to the rest of the cell.
This formula goes through a set of data and returns the unique values from the set. It is written in the following syntax.
Here, an array refers to a range of 1-dimensional data, meaning it can only be along a row or a column.
The VLOOKUP is one of the most useful formulas for a marketer. It searches a range using a string and returns matching values from a specific cell in the range.
The syntax is given below.
=VLOOKUP(string, range, index_key)
In the example below, we use the VLOOKUP function to search for the sales figure of Chuck.
You can also perform VLOOKUP from another sheet to fetch the values from the other sheet.
The import formulas in Google Sheets lets you import data from external sources without breaking a sweat. There are two import functions that come in handy very frequently – IMPORTRANGE and IMPORTXML.
The IMPORTRANGE function is used to import data from other spreadsheets without having to manually copy and paste the data.
The syntax is as follows:
Here spreadsheet_url refers to the URL of your other Google Sheet and range_string is the data that you wish to import from another spreadsheet.
There is a particular format in which you need to write range_string. It includes the name of the tab, followed by an exclamation character (!), and the range of cells to be imported. All of this is written within double quotes.
The IMPORTXML function lets you copy a table from a website thus reducing the hassle of manually copying the data. It lets you import several kinds of data forms including XML, HTML, CSV, TSV, and RSS among others. In the formula, the URL argument refers to the URL of the source page while the xpath_query is the XPath Query run on the data of the given URL.
The syntax for the IMPORTXML function is
Importing from XML queries may give a quite messy result if you do not use the proper queries.
The formulas mentioned here are the ones that you may generally need. Depending on your specific professional requirements, you may need to use other formulas that are not been mentioned here. In fact, some basic formulas for inserting date and time, as well as performing basic arithmetic operations are frequently used but are not covered here because they’re pretty easy and straightforward.