Google Sheets is a powerful tool for organizing, analyzing, and manipulating data. One of the key features that makes Google Sheets so versatile is its extensive library of formulas. Whether you're a beginner or an experienced user, having a handy Google Sheets formulas cheat sheet can save you time and help you work more efficiently. In this article, we'll explore some of the most useful formulas in Google Sheets and provide examples to help you master your spreadsheets.

## 1. Basic Arithmetic Formulas (Google Sheets Formulas Cheat Sheet)

Let's start with the basics. Google Sheets supports all the standard arithmetic operations, such as addition, subtraction, multiplication, and division. Here are some examples:

`=A1+B1`

: Adds the values in cells A1 and B1`=A1-B1`

: Subtracts the value in cell B1 from the value in cell A1`=A1*B1`

: Multiplies the values in cells A1 and B1`=A1/B1`

: Divides the value in cell A1 by the value in cell B1

You can also use parentheses to control the order of operations, just like in standard arithmetic. For example:

`=(A1+B1)*C1`

: Adds the values in cells A1 and B1, then multiplies the result by the value in cell C1

### Example 1: Calculating Total Sales

Suppose you have a spreadsheet with sales data for different products. You have the quantity sold in column A and the price per unit in column B. To calculate the total sales for each product, you can use the multiplication formula:

- In cell C1, enter the formula
`=A1*B1`

- Copy the formula down the column to calculate the total sales for each product

## 2. Text Manipulation Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets also offers a variety of formulas for working with text data. Here are a few commonly used text formulas:

`=CONCATENATE(A1, " ", B1)`

: Combines the text in cells A1 and B1, with a space in between`=LEFT(A1, 3)`

: Extracts the first 3 characters from the text in cell A1`=RIGHT(A1, 3)`

: Extracts the last 3 characters from the text in cell A1`=MID(A1, 2, 4)`

: Extracts 4 characters from the text in cell A1, starting from the 2nd character`=LEN(A1)`

: Returns the number of characters in the text in cell A1

### Example 2: Extracting First and Last Names

Let's say you have a spreadsheet with full names in column A, and you want to separate them into first and last names. You can use the `LEFT`

, `RIGHT`

, and `FIND`

formulas to accomplish this:

- In cell B1, enter the formula
`=LEFT(A1, FIND(" ", A1)-1)`

to extract the first name - In cell C1, enter the formula
`=RIGHT(A1, LEN(A1)-FIND(" ", A1))`

to extract the last name - Copy the formulas down the columns to extract the first and last names for each person

Anakin AI is the Best AI Automation Platform for your AI Automation!

Connect Your Google Sheets to Anakin AI, and build a Customized Workflow with a No Code AI App Builder!

👇👇

## 3. Date and Time Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets has a set of formulas for working with dates and times. Here are a few useful ones:

`=TODAY()`

: Returns the current date`=NOW()`

: Returns the current date and time`=DATE(2023, 6, 15)`

: Returns the date June 15, 2023`=YEAR(A1)`

: Extracts the year from the date in cell A1`=MONTH(A1)`

: Extracts the month from the date in cell A1`=DAY(A1)`

: Extracts the day from the date in cell A1`=WEEKDAY(A1)`

: Returns the day of the week for the date in cell A1 (1 = Sunday, 2 = Monday, etc.)

### Example 3: Calculating Age from Date of Birth

Suppose you have a spreadsheet with dates of birth in column A, and you want to calculate the current age of each person. You can use the `DATEDIF`

formula to do this:

- In cell B1, enter the formula
`=DATEDIF(A1, TODAY(), "Y")`

to calculate the age in years - Copy the formula down the column to calculate the age for each person

## 4. Conditional Formulas (Google Sheets Formulas Cheat Sheet)

Conditional formulas allow you to perform different calculations based on certain conditions. The most commonly used conditional formula in Google Sheets is `IF`

. Here's the basic syntax:

`=IF(condition, value_if_true, value_if_false)`

For example:

`=IF(A1>10, "Greater than 10", "Less than or equal to 10")`

: Returns "Greater than 10" if the value in cell A1 is greater than 10, otherwise returns "Less than or equal to 10"

You can also nest multiple `IF`

statements to create more complex conditions:

`=IF(A1>10, "Greater than 10", IF(A1>5, "Greater than 5", "Less than or equal to 5"))`

### Example 4: Applying Conditional Formatting

Let's say you want to highlight cells in column A that contain values greater than 100. You can use conditional formatting with the `IF`

formula to achieve this:

- Select the range of cells you want to format (e.g., A1:A100)
- Go to Format > Conditional formatting
- In the "Format cells if" dropdown, select "Custom formula is"
- Enter the formula
`=A1>100`

- Click "Done" to apply the formatting

Now, any cells in column A with values greater than 100 will be highlighted based on your chosen formatting.

## 5. Lookup and Reference Formulas (Google Sheets Formulas Cheat Sheet)

Google Sheets offers several formulas for looking up and referencing data from other cells or sheets. Here are a few commonly used ones:

`=VLOOKUP(search_key, range, index, [is_sorted])`

: Searches for a value in the leftmost column of a range and returns the corresponding value from a specified column`=HLOOKUP(search_key, range, index, [is_sorted])`

: Similar to VLOOKUP, but searches for a value in the top row of a range and returns the corresponding value from a specified row`=INDEX(range, row, [column])`

: Returns the value at the specified row and column of a range`=MATCH(search_key, range, [search_type])`

: Returns the relative position of a value in a range

### Example 5: Using VLOOKUP to Retrieve Product Prices

Suppose you have a spreadsheet with product names in column A and their corresponding prices in another sheet named "Prices," where the product names are in column A and the prices are in column B. You can use the `VLOOKUP`

formula to retrieve the prices in your main sheet:

- In cell B1 of your main sheet, enter the formula
`=VLOOKUP(A1, Prices!A:B, 2, FALSE)`

- Copy the formula down the column to retrieve the prices for each product

Here are 10 more examples of useful Google Sheets formulas to add to your cheat sheet, along with detailed explanations to help you master your spreadsheets:

## 6. COUNTIF (Google Sheets Formulas Cheat Sheet)

The `COUNTIF`

formula allows you to count the number of cells in a range that meet a specific criterion. Here's the syntax:

`=COUNTIF(range, criterion)`

For example:

`=COUNTIF(A1:A100, ">50")`

: Counts the number of cells in the range A1:A100 that are greater than 50`=COUNTIF(B1:B100, "Apples")`

: Counts the number of cells in the range B1:B100 that contain the text "Apples"

### Example 6: Counting the Number of Sales Above a Threshold

Suppose you have a spreadsheet with sales data, and you want to count the number of sales that exceed $1,000. You can use the `COUNTIF`

formula to achieve this:

- In cell B1, enter the formula
`=COUNTIF(A1:A100, ">1000")`

- The result will show the number of sales in the range A1:A100 that are greater than $1,000

## 7. SUMIF (Google Sheets Formulas Cheat Sheet)

The `SUMIF`

formula allows you to sum the values in a range that meet a specific criterion. Here's the syntax:

`=SUMIF(range, criterion, [sum_range])`

For example:

`=SUMIF(A1:A100, ">50", B1:B100)`

: Sums the values in the range B1:B100 where the corresponding values in the range A1:A100 are greater than 50`=SUMIF(A1:A100, "Apples", B1:B100)`

: Sums the values in the range B1:B100 where the corresponding values in the range A1:A100 contain the text "Apples"

### Example 7: Calculating Total Sales for a Specific Product

Let's say you have a spreadsheet with product names in column A and their corresponding sales amounts in column B. You want to calculate the total sales for a specific product, such as "Apples." You can use the `SUMIF`

formula to do this:

- In cell C1, enter the formula
`=SUMIF(A1:A100, "Apples", B1:B100)`

- The result will show the total sales for "Apples" based on the data in the ranges A1:A100 and B1:B100

## 8. AVERAGEIF (Google Sheets Formulas Cheat Sheet)

The `AVERAGEIF`

formula allows you to calculate the average of values in a range that meet a specific criterion. Here's the syntax:

`=AVERAGEIF(range, criterion, [average_range])`

For example:

`=AVERAGEIF(A1:A100, ">50", B1:B100)`

: Calculates the average of the values in the range B1:B100 where the corresponding values in the range A1:A100 are greater than 50`=AVERAGEIF(A1:A100, "Apples", B1:B100)`

: Calculates the average of the values in the range B1:B100 where the corresponding values in the range A1:A100 contain the text "Apples"

### Example 8: Calculating the Average Price of a Specific Product

Suppose you have a spreadsheet with product names in column A and their corresponding prices in column B. You want to calculate the average price of a specific product, such as "Oranges." You can use the `AVERAGEIF`

formula to achieve this:

- In cell C1, enter the formula
`=AVERAGEIF(A1:A100, "Oranges", B1:B100)`

- The result will show the average price of "Oranges" based on the data in the ranges A1:A100 and B1:B100

## 9. COUNTIFS (Google Sheets Formulas Cheat Sheet)

The `COUNTIFS`

formula is an extension of the `COUNTIF`

formula, allowing you to count the number of cells that meet multiple criteria across different ranges. Here's the syntax:

`=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])`

For example:

`=COUNTIFS(A1:A100, ">50", B1:B100, "<100")`

: Counts the number of cells where the values in the range A1:A100 are greater than 50 and the corresponding values in the range B1:B100 are less than 100

### Example 9: Counting the Number of Sales Within a Specific Range

Let's say you have a spreadsheet with sales data, and you want to count the number of sales between $500 and $1,000. You can use the `COUNTIFS`

formula to do this:

- In cell C1, enter the formula
`=COUNTIFS(A1:A100, ">500", A1:A100, "<1000")`

- The result will show the number of sales in the range A1:A100 that are between $500 and $1,000

## 10. SUMIFS (Google Sheets Formulas Cheat Sheet)

The `SUMIFS`

formula is an extension of the `SUMIF`

formula, allowing you to sum values in a range that meet multiple criteria across different ranges. Here's the syntax:

`=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])`

For example:

`=SUMIFS(C1:C100, A1:A100, ">50", B1:B100, "<100")`

: Sums the values in the range C1:C100 where the corresponding values in the range A1:A100 are greater than 50 and the corresponding values in the range B1:B100 are less than 100

### Example 10: Calculating Total Sales for a Specific Product Within a Date Range

Suppose you have a spreadsheet with product names in column A, sales dates in column B, and sales amounts in column C. You want to calculate the total sales for a specific product, such as "Bananas," within a specific date range. You can use the `SUMIFS`

formula to achieve this:

- In cell D1, enter the formula
`=SUMIFS(C1:C100, A1:A100, "Bananas", B1:B100, ">2023-01-01", B1:B100, "<2023-12-31")`

- The result will show the total sales for "Bananas" within the specified date range based on the data in the ranges A1:A100, B1:B100, and C1:C100

## 11. IFERROR (Google Sheets Formulas Cheat Sheet)

The `IFERROR`

formula allows you to handle errors in your formulas by providing an alternative value or action when an error occurs. Here's the syntax:

`=IFERROR(value, value_if_error)`

For example:

`=IFERROR(A1/B1, "Division by zero")`

: Returns the result of dividing the value in cell A1 by the value in cell B1, but if a division by zero error occurs, it returns the text "Division by zero"

### Example 11: Handling Division by Zero Errors

Let's say you have a spreadsheet with numbers in column A and column B, and you want to divide the values in column A by the corresponding values in column B. However, some cells in column B might contain zeros, which would result in division by zero errors. You can use the `IFERROR`

formula to handle these errors:

- In cell C1, enter the formula
`=IFERROR(A1/B1, "N/A")`

- Copy the formula down the column to divide the values in column A by the corresponding values in column B, but if a division by zero error occurs, it will display "N/A" instead

Anakin AI is the Best AI Automation Platform for your AI Automation!

Connect Your Google Sheets to Anakin AI, and build a Customized Workflow with a No Code AI App Builder!

👇👇

## 12. VLOOKUP with IFERROR (Google Sheets Formulas Cheat Sheet)

Combining the `VLOOKUP`

formula with the `IFERROR`

formula allows you to handle cases where the lookup value is not found in the lookup range. Here's an example:

`=IFERROR(VLOOKUP(A1, B1:C100, 2, FALSE), "Not found")`

This formula looks up the value in cell A1 in the range B1:C100 and returns the corresponding value from the second column of the range. If the lookup value is not found, it returns the text "Not found" instead of an error.

### Example 12: Looking Up Product Prices with Error Handling

Suppose you have a spreadsheet with product names in column A and another sheet named "Prices" with product names in column A and their corresponding prices in column B. You want to look up the prices of the products in your main sheet, but some products might not be listed in the "Prices" sheet. You can use the `VLOOKUP`

formula with `IFERROR`

to handle these cases:

- In cell B1 of your main sheet, enter the formula
`=IFERROR(VLOOKUP(A1, Prices!A:B, 2, FALSE), "Price not available")`

- Copy the formula down the column to look up the prices for each product, but if a product is not found in the "Prices" sheet, it will display "Price not available" instead of an error

## 13. UNIQUE (Google Sheets Formulas Cheat Sheet)

The `UNIQUE`

formula allows you to extract unique values from a range or array. Here's the syntax:

`=UNIQUE(range)`

For example:

`=UNIQUE(A1:A100)`

: Returns a list of unique values from the range A1:A100

### Example 13: Extracting Unique Product Names

Let's say you have a spreadsheet with product names in column A, and you want to create a list of unique product names. You can use the `UNIQUE`

formula to achieve this:

- In cell B1, enter the formula
`=UNIQUE(A1:A100)`

- The result will be a list of unique product names from the range A1:A100

## 14. FILTER (Google Sheets Formulas Cheat Sheet)

The `FILTER`

formula allows you to filter a range or array based on one or more criteria. Here's the syntax:

`=FILTER(range, condition1, [condition2, ...])`

For example:

`=FILTER(A1:B100, B1:B100>50)`

: Returns the values from the range A1:B100 where the corresponding values in the range B1:B100 are greater than 50

### Example 14: Filtering Sales Data Based on a Threshold

Suppose you have a spreadsheet with product names in column A and sales amounts in column B. You want to create a list of products with sales greater than $1,000. You can use the `FILTER`

formula to do this:

- In cell C1, enter the formula
`=FILTER(A1:A100, B1:B100>1000)`

- The result will be a list of products from the range A1:A100 where the corresponding sales amounts in the range B1:B100 are greater than $1,000

## 15. QUERY (Google Sheets Formulas Cheat Sheet)

The `QUERY`

formula allows you to perform SQL-like queries on your spreadsheet data. It's a powerful formula that can be used for filtering, sorting, and aggregating data. Here's a basic example of the syntax:

`=QUERY(data, query, [headers])`

For example:

`=QUERY(A1:C100, "SELECT A, SUM(B) GROUP BY A", 1)`

: Groups the data in the range A1:C100 by the values in column A and calculates the sum of the values in column B for each group, with headers in the first row

### Example 15: Summarizing Sales Data by Product

Let's say you have a spreadsheet with product names in column A, sales dates in column B, and sales amounts in column C. You want to create a summary of total sales for each product. You can use the `QUERY`

formula to achieve this:

- In cell D1, enter the formula
`=QUERY(A1:C100, "SELECT A, SUM(C) GROUP BY A LABEL SUM(C) 'Total Sales'", 1)`

- The result will be a summary table with product names and their corresponding total sales, based on the data in the range A1:C100

## Conclusion

This Google Sheets formulas cheat sheet, now expanded with 10 more examples, covers an even wider range of formulas that can help you work more efficiently and effectively with your spreadsheet data. From conditional counting and summing to error handling, unique value extraction, filtering, and powerful querying, these formulas offer a wealth of possibilities for data analysis and manipulation.

As you continue to explore and apply these formulas in your own work, remember that practice is key to mastering them. Don't hesitate to experiment with different combinations and variations to find the best solutions for your specific needs.

Keep this cheat sheet handy as a reference, and feel free to add your own examples and notes as you discover new ways to use these formulas. With time and experience, you'll develop a robust toolkit of Google Sheets formulas that will make you a true spreadsheet pro.

Happy formula-building!

## FAQ: Google Sheets Formulas

### What are the most useful formulas in Google Sheets?

Some of the most useful formulas in Google Sheets include:

- VLOOKUP: Looks up and retrieves data from a specific column in a table
- ARRAYFORMULA: Applies a formula to an entire range of cells
- SUM: Adds up a range of cells
- IF: Performs a logical test and returns a value based on the result
- IMPORTRANGE: Imports data from another Google Sheets spreadsheet

### What are the basic math formulas for Google Sheets?

The basic math formulas in Google Sheets include:

- SUM: Adds up a range of cells
- AVERAGE: Calculates the average of a range of cells
- COUNT: Counts the number of cells that contain numbers
- MAX: Returns the highest value in a range of cells
- MIN: Returns the lowest value in a range of cells

### How to write Google Sheets formulas?

To write a formula in Google Sheets:

- Click on the cell where you want to enter the formula
- Type an equals sign (=) to begin the formula
- Enter the formula using cell references, functions, and operators
- Press Enter to calculate the result

### What is <> in Google Sheet formula?

In Google Sheets formulas, the `<>`

operator means "not equal to." It is used to compare two values and returns TRUE if they are not equal, and FALSE if they are equal.

Anakin AI is the Best AI Automation Platform for your AI Automation!

Connect Your Google Sheets to Anakin AI, and build a Customized Workflow with a No Code AI App Builder!

👇👇