## Top 101 Formulas You Need to Know to Work with Google Sheets

Google Sheets is a powerful spreadsheet application that allows users to organize, analyze, and manipulate data effectively. To become proficient in using Google Sheets, it’s essential to familiarize yourself with various formulas that can perform complex calculations and automate tasks. In this article, we will explore the top 101 formulas that you should know to work efficiently with Google Sheets. Let’s dive in:

## 1. SUM

**Formula:** `=SUM(range)`

The SUM formula adds up the values within a specified range of cells.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=SUM(A1:A3)`

will yield `60`

.

## 2. AVERAGE

**Formula:** `=AVERAGE(range)`

The AVERAGE formula calculates the arithmetic mean of the values in the specified range.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=AVERAGE(A1:A3)`

will yield `20`

.

## 3. MIN

**Formula:** `=MIN(range)`

The MIN formula returns the smallest value from the specified range.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=MIN(A1:A3)`

will yield `10`

.

## 4. MAX

**Formula:** `=MAX(range)`

The MAX formula returns the largest value from the specified range.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=MAX(A1:A3)`

will yield `30`

.

## 5. COUNT

**Formula:** `=COUNT(range)`

The COUNT formula counts the number of cells that contain numerical values in the specified range.

**Example:**

A | |
---|---|

10 | |

30 |

`=COUNT(A1:A3)`

will yield `2`

.

## 6. COUNTA

**Formula:** `=COUNTA(range)`

The COUNTA formula counts the number of non-empty cells in the specified range, including text and numerical values.

**Example:**

A | |
---|---|

10 | |

ABC | |

30 |

`=COUNTA(A1:A3)`

will yield `3`

.

## 7. COUNTIF

**Formula:** `=COUNTIF(range, criteria)`

The COUNTIF formula counts the number of cells in the specified range that meet the given criteria.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=COUNTIF(A1:A3, ">15")`

will yield `2`

.

## 8. SUMIF

**Formula:** `=SUMIF(range, criteria, sum_range)`

The SUMIF formula adds up the values in the `sum_range`

that meet the specified `criteria`

in the given `range`

.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=SUMIF(A1:A3, ">15", B1:B3)`

will yield `50`

.

## 9. AVERAGEIF

**Formula:** `=AVERAGEIF(range, criteria, average_range)`

The AVERAGEIF formula calculates the average of the values in the `average_range`

that meet the specified `criteria`

in the given `range`

.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=AVERAGEIF(A1:A3, ">15", B1:B3)`

will yield `25`

.

## 10. SUMIFS

**Formula:** `=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

The SUMIFS formula adds up the values in the `sum_range`

that meet multiple criteria specified in different ranges.

**Example:**

A | B | |
---|---|---|

10 | 5 | |

20 | 5 | |

30 | 2 |

`=SUMIFS(C1:C3, A1:A3, ">15", B1:B3, ">=5")`

will yield `0`

.

## 11. AVERAGEIFS

**Formula:** `=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

The AVERAGEIFS formula calculates the average of the values in the `average_range`

that meet multiple criteria specified in different ranges.

**Example:**

A | B | |
---|---|---|

10 | 5 | |

20 | 5 | |

30 | 2 |

`=AVERAGEIFS(C1:C3, A1:A3, ">15", B1:B3, ">=5")`

will yield `0`

.

## 12. VLOOKUP

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

The VLOOKUP formula searches for a value in the first column of a specified range and returns a value from a different column in the same row.

**Example:**

A | B |
---|---|

100 | Red |

200 | Blue |

300 | Green |

`=VLOOKUP(200, A1:B3, 2, FALSE)`

will yield `Blue`

.

## 13. HLOOKUP

**Formula:** `=HLOOKUP(search_key, range, index, [is_sorted])`

The HLOOKUP formula searches for a value in the first row of a specified range and returns a value from a different row in the same column.

**Example:**

A | B | C |
---|---|---|

Apple | 100 | Red |

Banana | 200 | Yellow |

Orange | 300 | Orange |

`=HLOOKUP("Banana", A1:C3, 3, FALSE)`

will yield `Yellow`

.

## 14. INDEX

**Formula:** `=INDEX(range, row_num, [column_num])`

The INDEX formula returns the value of a cell within a specified range based on the given row and column numbers.

**Example:**

A | B |
---|---|

100 | Red |

200 | Blue |

300 | Green |

`=INDEX(A1:B3, 2, 2)`

will yield `Blue`

.

## 15. MATCH

**Formula:** `=MATCH(search_key, range, [search_type])`

The MATCH formula searches for a value within a specified range and returns the relative position of the value found.

**Example**

A |
---|

Red |

Blue |

Green |

`=MATCH("Blue", A1:A3, 0)`

will yield `2`

. The `0`

as the `[search_type]`

parameter indicates an exact match. In this example, the MATCH formula returns `2`

, indicating that “Blue” is found in the second row of the range A1:A3.

## 16. IF

**Formula:** `=IF(logical_expression, value_if_true, value_if_false)`

The IF formula evaluates a logical expression and returns one value if the expression is true and another value if it is false.

**Example:**

A | |
---|---|

10 | |

20 | |

30 |

`=IF(A1>20, "Greater than 20", "Not greater than 20")`

will yield `"Not greater than 20"`

.

## 17. AND

**Formula:** `=AND(logical_expression1, [logical_expression2], ...)`

The AND formula checks if all the specified logical expressions are true. It returns `TRUE`

if all expressions are true; otherwise, it returns `FALSE`

.

**Example:**

`=AND(A1>10, B1<50)`

will return `TRUE`

if both cell A1 is greater than 10 and cell B1 is less than 50.

## 18. OR

**Formula:** `=OR(logical_expression1, [logical_expression2], ...)`

The OR formula checks if at least one of the specified logical expressions is true. It returns `TRUE`

if at least one expression is true; otherwise, it returns `FALSE`

.

**Example:**

`=OR(A1>10, B1<50)`

will return `TRUE`

if either cell A1 is greater than 10 or cell B1 is less than 50.

## 19. NOT

**Formula:** `=NOT(logical_expression)`

The NOT formula reverses the logical value of the specified expression. It returns `TRUE`

if the expression is false and `FALSE`

if the expression is true.

**Example:**

`=NOT(A1>10)`

will return `FALSE`

if cell A1 is greater than 10; otherwise, it will return `TRUE`

.

## 20. CONCATENATE

**Formula:** `=CONCATENATE(text1, [text2], ...)`

The CONCATENATE formula combines multiple strings or cell values into a single text string.

**Example:**

A | B | C |
---|---|---|

John | Doe | JohnDoe |

`=CONCATENATE(A1, B1)`

will yield `JohnDoe`

.

Alternatively, you can use the `&`

operator:

`=A1 & B1`

will yield the same result.

## 21. TEXT

**Formula:** `=TEXT(value, format)`

The TEXT formula converts a numeric value into a text string based on the specified format.

**Example:**

A | B |
---|---|

0.15 | 15.00% |

`=TEXT(A1, "0.00%")`

will yield `15.00%`

.

## 22. LEN

**Formula:** `=LEN(text)`

The LEN formula returns the number of characters in a given text string.

**Example:**

`=LEN("Hello, World!")`

will yield `13`

.

## 23. TRIM

**Formula:** `=TRIM(text)`

The TRIM formula removes any leading or trailing spaces from a text string.

**Example:**

`=TRIM(" Hello, World! ")`

will yield `"Hello, World!"`

.

## 24. LOWER

**Formula:** `=LOWER(text)`

The LOWER formula converts all the characters in a text string to lowercase.

**Example:**

`=LOWER("Hello, World!")`

will yield `"hello, world!"`

.

## 25. UPPER

**Formula:** `=UPPER(text)`

The UPPER formula converts all the characters in a text string to uppercase.

**Example:**

`=UPPER("Hello, World!")`

will yield `"HELLO, WORLD!"`

.

## 26. PROPER

**Formula:** `=PROPER(text)`

The PROPER formula capitalizes the first letter of each word in a text string.

**Example:**

`=PROPER("hello, world!")`

will yield `"Hello, World!"`

.

## 27. SUBSTITUTE

**Formula:** `=SUBSTITUTE(text, old_text, new_text, [occurrence])`

The SUBSTITUTE formula replaces occurrences of a specified old text with new text in a text string.

**Example:**

`=SUBSTITUTE("Hello, World!", "Hello", "Hi")`

will yield `"Hi, World!"`

.

## 28. MID

**Formula:** `=MID(text, start, length)`

The MID formula extracts a specific number of characters from a text string, starting at a given position.

**Example:**

`=MID("Hello, World!", 7, 5)`

will yield `"World"`

.

## 29. LEFT

**Formula:** `=LEFT(text, num_chars)`

The LEFT formula returns a specified number of characters from the beginning of a text string.

**Example:**

`=LEFT("Hello, World!", 5)`

will yield `"Hello"`

.

## 30. RIGHT

**Formula:** `=RIGHT(text, num_chars)`

The RIGHT formula returns a specified number of characters from the end of a text string.

**Example:**

`=RIGHT("Hello, World!", 6)`

will yield `"World!"`

.

## 31. RAND

**Formula:** `=RAND()`

The RAND formula generates a random number between 0 and 1.

**Example:**

`=RAND()`

will yield a random decimal number between 0 and 1.

## 32. RANDBETWEEN

**Formula:** `=RANDBETWEEN(bottom, top)`

The RANDBETWEEN formula generates a random integer between the specified bottom and top values.

**Example:**

`=RANDBETWEEN(1, 100)`

will yield a random integer between 1 and 100.

## 33. NOW

**Formula:** `=NOW()`

The NOW formula returns the current date and time.

**Example:**

`=NOW()`

will yield the current date and time.

## 34. TODAY

**Formula:** `=TODAY()`

The TODAY formula returns the current date.

**Example:**

`=TODAY()`

will yield the current date.

## 35. DATE

**Formula:** `=DATE(year, month, day)`

The DATE formula creates a date based on the specified year, month, and day.

**Example:**

`=DATE(2023, 7, 28)`

will yield `7/28/2023`

.

## 36. TIME

**Formula:** `=TIME(hour, minute, second)`

The TIME formula creates a time based on the specified hour, minute, and second.

**Example:**

`=TIME(12, 30, 0)`

will yield `12:30:00 PM`

.

## 37. EOMONTH

**Formula:** `=EOMONTH(start_date, months)`

The EOMONTH formula calculates the end of the month date after adding or subtracting a specified number of months from the given start_date.

**Example:**

Let’s assume cell A1 contains the date `2023-07-15`

.

`=EOMONTH(A1, 1)`

will yield `2023-08-31`

, which is the end of the month after adding one month to the date in cell A1.

`=EOMONTH(A1, -1)`

will yield `2023-06-30`

, which is the end of the month after subtracting one month from the date in cell A1.

## 38. DATEDIF

**Formula:** `=DATEDIF(start_date, end_date, unit)`

The DATEDIF formula calculates the difference between two dates in years, months, or days, based on the specified unit.

**Example:**

Let’s assume cell A1 contains the start date `2023-01-01`

, and cell B1 contains the end date `2023-07-28`

.

`=DATEDIF(A1, B1, "Y")`

will yield `0`

, as there is no full year between the start and end dates.

`=DATEDIF(A1, B1, "M")`

will yield `6`

, as there are six full months between the start and end dates.

`=DATEDIF(A1, B1, "D")`

will yield `209`

, as there are 209 days between the start and end dates.

## 39. NETWORKDAYS

**Formula:** `=NETWORKDAYS(start_date, end_date, [holidays])`

The NETWORKDAYS formula calculates the number of working days between two dates, excluding specified holidays.

**Example:**

Let’s assume cell A1 contains the start date `2023-07-15`

, and cell B1 contains the end date `2023-07-28`

.

`=NETWORKDAYS(A1, B1)`

will yield `10`

, assuming there are no holidays between the start and end dates.

## 40. TODAY

**Formula:** `=TODAY()`

The TODAY formula returns the current date.

**Example:**

`=TODAY()`

will yield the current date.

## 41. NOW

**Formula:** `=NOW()`

The NOW formula returns the current date and time.

**Example:**

`=NOW()`

will yield the current date and time.

## 42. COUNTBLANK

**Formula:** `=COUNTBLANK(range)`

The COUNTBLANK formula counts the number of empty cells within a specified range.

**Example:**

Let’s assume cells A1 to A5 contain the following values: `10`

, `, `20`, `

, `30`

.

`=COUNTBLANK(A1:A5)`

will yield `2`

, as there are two empty cells in the range.

## 43. IFERROR

**Formula:** `=IFERROR(value, value_if_error)`

The IFERROR formula checks if a formula returns an error and provides an alternative value or action if an error occurs.

**Example:**

Let’s assume cell A1 contains the value `10`

, and cell B1 contains the value `0`

.

`=A1/B1`

will result in a `#DIV/0!`

error.

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

will yield `"Error: Division by zero"`

, indicating the presence of an error in the formula.

## 44. SUMPRODUCT

**Formula:** `=SUMPRODUCT(array1, [array2], ...)`

The SUMPRODUCT formula calculates the sum of the products of corresponding elements in multiple arrays.

**Example:**

Let’s assume cells A1 to A3 contain the values `2`

, `3`

, and `4`

, and cells B1 to B3 contain the values `5`

, `6`

, and `7`

.

`=SUMPRODUCT(A1:A3, B1:B3)`

will yield `56`

, calculated as `(2*5) + (3*6) + (4*7)`

.

## 45. TRANSPOSE

**Formula:** `=TRANSPOSE(array)`

The TRANSPOSE formula changes the orientation of a range, converting rows into columns and vice versa.

**Example:**

Let’s assume cells A1 to C1 contain the values `1`

, `2`

, and `3`

.

`=TRANSPOSE(A1:C1)`

will yield a vertical column with three cells containing `1`

, `2`

, and `3`

.

## 46. INDEX MATCH (Combining INDEX and MATCH)

The INDEX MATCH combination is a powerful way to perform lookups in Google Sheets when VLOOKUP or HLOOKUP is not sufficient. It allows you to find a value in one column based on a match in another column.

**Example:**

Let’s consider the following table:

A | B |
---|---|

Red | 100 |

Blue | 200 |

Green | 300 |

Assume that cell C1 contains the value `"Blue"`

. To retrieve the corresponding value from column B, we use the INDEX MATCH combination:

`=INDEX(B1:B3, MATCH(C1, A1:A3, 0))`

will yield `200`

, as it finds the value `"Blue"`

in column A and returns the corresponding value from column B.

## 47. UNIQUE

**Formula:** `=UNIQUE(range)`

The UNIQUE formula returns a list of unique values from a specified range, removing any duplicates.

**Example:**

Let’s assume cells A1 to A5 contain the values `10`

, `20`

, `10`

, `30`

, and `20`

.

`=UNIQUE(A1:A5)`

will yield a vertical column with three cells containing `10`

, `20`

, and `30`

.

## 48. SORT

**Formula:** `=SORT(range, sort_column, [sort_order1], [sort_column2], [sort_order2], ...)`

The SORT formula arranges a range of cells in ascending or descending order based on specified sort columns.

**Example:**

Let’s assume cells A1 to B5 contain the following values:

A | B |
---|---|

John | 100 |

Alice | 90 |

Bob | 120 |

Emma | 80 |

David | 110 |

`=SORT(A1:B5, 2, FALSE)`

will yield the following sorted table:

A | B |
---|---|

Bob | 120 |

John | 100 |

David | 110 |

Alice | 90 |

Emma | 80 |

The `2`

indicates that the sorting should be based on values in column B (the second column), and `FALSE`

indicates descending order.

## 49. FILTER

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

The FILTER formula returns a filtered range of cells that meet the specified conditions.

**Example:**

Let’s assume cells A1 to B5 contain the following values:

A | B |
---|---|

John | 100 |

Alice | 90 |

Bob | 120 |

Emma | 80 |

David | 110 |

`=FILTER(A1:B5, B1:B5>=100)`

will yield the following filtered table:

A | B |
---|---|

John | 100 |

Bob | 120 |

David | 110 |

The formula filtered the original table, showing only the rows where the value in column B is greater than or equal to 100.

## 50. QUERY

**Formula:** `=QUERY(data, query, [headers])`

The QUERY formula allows you to perform SQL-like queries on your data within Google Sheets.

**Example:**

Let’s assume cells A1 to B5 contain the same data as in the previous example:

A | B |
---|---|

John | 100 |

Alice | 90 |

Bob | 120 |

Emma | 80 |

David | 110 |

`=QUERY(A1:B5, "SELECT A, B WHERE B >= 100", 1)`

will yield the following result:

A | B |
---|---|

John | 100 |

Bob | 120 |

David | 110 |

The formula uses a SQL-like query to select only the rows where the value in column B is greater than or equal to 100. The `1`

at the end indicates that the data has headers.

## 51. SUMPRODUCT with Multiple Criteria

The SUMPRODUCT formula can be used with multiple criteria to perform complex calculations.

**Example:**

Let’s assume cells A1 to C5 contain the following values:

A | B | C |
---|---|---|

John | 100 | Yes |

Alice | 90 | No |

Bob | 120 | Yes |

Emma | 80 | Yes |

David | 110 | No |

To calculate the sum of the values in column B where column A is “John” and column C is “Yes,” you can use:

`=SUMPRODUCT((A1:A5="John")*(C1:C5="Yes")*(B1:B5))`

This formula will yield `100`

, as it adds up the values in column B that meet both criteria: “John” in column A and “Yes” in column C.

## 52. SUMIFS with Multiple Criteria

The SUMIFS formula can also be used with multiple criteria to calculate sums based on different conditions.

**Example:**

Let’s assume cells A1 to C5 contain the same data as in the previous example:

A | B | C |
---|---|---|

John | 100 | Yes |

Alice | 90 | No |

Bob | 120 | Yes |

Emma | 80 | Yes |

David | 110 | No |

To calculate the sum of the values in column B where column A is “John” and column C is “Yes,” you can use:

`=SUMIFS(B1:B5, A1:A5, "John", C1:C5, "Yes")`

This formula will also yield `100`

, providing the same result as the SUMPRODUCT formula with multiple criteria.

## 53. CONCATENATEIF (Custom Function)

Google Sheets does not have a CONC

ATENATEIF function by default, but you can create a custom function using Google Apps Script to achieve this functionality. The custom function will concatenate values in one range based on specified criteria in another range.

Here’s an example of how to create the CONCATENATEIF custom function:

- Open your Google Sheets and click on “Extensions” in the top menu.
- Select “Apps Script.”
- In the Apps Script editor, paste the following code:

`function CONCATENATEIF(range, criteriaRange, criteria) {`

var output = "";

for (var i = 0; i < range.length; i++) {

if (criteriaRange[i][0] == criteria) {

output += range[i][0];

}

}

return output;

}

- Save the script (File > Save) and give it a name, such as “CONCATENATEIF.”

Now, you can use the CONCATENATEIF custom function in your Google Sheets:

Let’s assume cells A1 to A5 contain the values: “Apple”, “Orange”, “Banana”, “Grapes”, “Apple,” and cells B1 to B5 contain the corresponding quantities: 3, 2, 4, 5, 1.

`=CONCATENATEIF(A1:A5, B1:B5, 3)`

will yield `"AppleApple"`

as it concatenates all occurrences of “Apple” (criteria) based on the quantities in column B being equal to 3.

## 54. COUNTIFS with Multiple Criteria

The COUNTIFS formula can be used to count cells that meet multiple criteria specified in different ranges.

**Example:**

Let’s assume cells A1 to C5 contain the following values:

A | B | C |
---|---|---|

John | 100 | Yes |

Alice | 90 | No |

Bob | 120 | Yes |

Emma | 80 | Yes |

David | 110 | No |

To count the number of occurrences where column A is “John” and column C is “Yes,” you can use:

`=COUNTIFS(A1:A5, "John", C1:C5, "Yes")`

This formula will yield `1`

, as there is only one occurrence that meets both criteria.

## 55. AVERAGEIF with Multiple Criteria

The AVERAGEIF formula can also be used with multiple criteria to calculate the average based on different conditions.

**Example:**

Let’s assume cells A1 to C5 contain the same data as in the previous example:

A | B | C |
---|---|---|

John | 100 | Yes |

Alice | 90 | No |

Bob | 120 | Yes |

Emma | 80 | Yes |

David | 110 | No |

To calculate the average of the values in column B where column A is “John” and column C is “Yes,” you can use:

`=AVERAGEIF(A1:A5, "John", B1:B5)`

This formula will yield `100`

, as it calculates the average of the values in column B that meet the criteria “John” in column A.

## 56. VLOOKUP with Approximate Match

By default, VLOOKUP performs an exact match. However, you can use the optional fourth parameter (range_lookup) to allow for approximate matches.

**Example:**

Let’s assume cells F1 to G3 contain the following data:

F | G |
---|---|

1500 | Low |

3000 | Medium |

5000 | High |

Let’s also assume cell E1 contains the value `4200`

.

`=VLOOKUP(E1, F1:G3, 2, TRUE)`

will yield `"Medium"`

, as it performs an approximate match and finds the value closest to `4200`

in column F, which is `3000`

. The corresponding value in column G is `"Medium"`

, so that’s what the formula returns.

It’s important to note that for the VLOOKUP to work with an approximate match, the data in the first column (F in this case) should be sorted in ascending order.

## 57. HLOOKUP with Approximate Match

Similar to VLOOKUP, HLOOKUP can also perform an approximate match when you set the optional fourth parameter (range_lookup) to `TRUE`

.

**Example:**

Let’s assume cells F1 to H1 contain the following data:

F | G | H |
---|---|---|

1000 | 2000 | 3000 |

Let’s also assume cell E1 contains the value `2500`

.

`=HLOOKUP(E1, F1:H1, 1, TRUE)`

will yield `2000`

, as it performs an approximate match and finds the value closest to `2500`

in the row. The corresponding value in column G is `2000`

, so that’s what the formula returns.

## 58. Array Formulas

Array formulas in Google Sheets allow you to perform calculations on multiple cells and return multiple results.

**Example:**

Let’s assume cells A1 to A5 contain the values: `1`

, `2`

, `3`

, `4`

, and `5`

.

To calculate the sum of the squares of each number, you can use an array formula:

- Select cells B1 to B5.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=ARRAYFORMULA(A1:A5^2)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

A | B |
---|---|

1 | 1 |

2 | 4 |

3 | 9 |

4 | 16 |

5 | 25 |

The formula `A1:A5^2`

is automatically applied to each cell in the range B1:B5, calculating the square of each number in column A.

## 59. Nested IF Statements

You can use nested IF statements to handle multiple conditions in a single formula.

**Example:**

Let’s assume cell A1 contains the value `70`

.

`=IF(A1 >= 90, "A", IF(A1 >= 80, "B", IF(A1 >= 70, "C", IF(A1 >= 60, "D", "F"))))`

The above formula will yield `"C"`

, as the value in cell A1 is `70`

, and it falls within the range of 70-79, corresponding to the grade “C.”

## 60. WEEKDAY

**Formula:** `=WEEKDAY(date, [type])`

The WEEKDAY formula returns the day of the week for a given date as a number (1 for Sunday, 2 for Monday, and so on).

**Example:**

Let’s assume cell A1 contains the date `2023-07-28`

.

`=WEEKDAY(A1)`

will yield `6`

, as July 28, 2023, is a Friday (the 6th day of the week).

You can use the optional `[type]`

parameter to specify the starting day of the week. For example, `2`

will make Monday the first day of the week.

## 61. ISBLANK

**Formula:** `=ISBLANK(value)`

The ISBLANK formula checks whether a cell is empty and returns `TRUE`

if the cell is blank and `FALSE`

if it is not.

**Example:**

Let’s assume cell A1 is empty.

`=ISBLANK(A1)`

will yield `TRUE`

, indicating that cell A1 is blank.

## 62. ISTEXT

**Formula:** `=ISTEXT(value)`

The ISTEXT formula checks whether a cell contains text and returns `TRUE`

if the cell contains text and `FALSE`

if it contains a number or is empty.

**Example:**

Let’s assume cell A1 contains the value `"Hello"`

.

`=ISTEXT(A1)`

will yield `TRUE`

, as cell A1 contains text.

## 63. ISNUMBER

**Formula:** `=ISNUMBER(value)`

The ISNUMBER formula checks whether a cell contains a numeric value and returns `TRUE`

if the cell contains a number and `FALSE`

if it contains text or is empty.

**Example:**

Let’s assume cell A1 contains the value `123`

.

`=ISNUMBER(A1)`

will yield `TRUE`

, as cell A1 contains a numeric value.

## 64. ISERROR

**Formula:** `=ISERROR(value)`

The ISERROR formula checks whether a cell contains an error and returns `TRUE`

if the cell contains an error and `FALSE`

if it contains a valid value.

**Example:**

Let’s assume cell A1 contains the formula `=1/0`

, which results in a `#DIV/0!`

error.

`=ISERROR(A1)`

will yield `TRUE`

, indicating that cell A1 contains an error.

## 65. ISNA

**Formula:** `=ISNA(value)`

The ISNA formula checks whether a cell contains the `#N/A`

error and returns `TRUE`

if the cell contains the `#N/A`

error and `FALSE`

if it contains a valid value or a different error.

**Example:**

Let’s assume cell A1 contains the formula `=VLOOKUP("Invalid Value", B1:C5, 2, FALSE)`

, which results in a `#N/A`

error.

`=ISNA(A1)`

will yield `TRUE`

, indicating that cell A1 contains the `#N/A`

error.

## 66. N

**Formula:** `=N(value)`

The N formula converts a value into a numeric value. If the input is text, it will return `0`

, and if the input is numeric, it will remain unchanged.

**Example:**

Let’s assume cell A1 contains the value `"123"`

.

`=N(A1)`

will yield `123`

, converting the text value into a numeric value.

## 67. UPPER, LOWER, and PROPER (with Non-English Characters)

The UPPER, LOWER, and PROPER formulas can also handle non-English characters and accents.

**Example:**

Let’s assume cell A1 contains the text `Café`

.

`=UPPER(A1)`

will yield `"CAFÉ"`

.

`=LOWER(A1)`

will yield `"café"`

.

`=PROPER(A1)`

will yield `"Café"`

.

These formulas maintain the non-English characters and accents while changing the case of the text.

## 68. INDEX MATCH with Multiple Criteria (Two or More Columns)

You can extend the INDEX MATCH combination to work with multiple criteria in two or more columns.

**Example:**

Let’s consider the following table:

A | B | C | |
---|---|---|---|

John | 25 | M | |

Alice | 30 | F | |

Bob | 28 | M | |

Emma | 22 | F | |

David | 35 | M |

Assume that cell E1 contains the name “Bob” and cell E2 contains the gender “M”. We want to retrieve the age of “Bob” with a gender of “M” from the table.

`=INDEX(B:B, MATCH(1, (A:A=E1)*(C:C=E2), 0))`

This is an array formula, so after entering it, press `Ctrl`

+ `Shift`

+ `Enter`

(or `Cmd`

+ `Shift`

+ `Enter`

on Mac) to apply it.

The formula will yield `28`

, which is Bob’s age, as it searches for a match in both the “Name” and “Gender” columns and returns the corresponding age from the “Age” column.

## 69. AVERAGEIFS with Multiple Criteria (Two or More Columns)

The AVERAGEIFS formula can handle multiple criteria in two or more columns to calculate averages based on different conditions.

**Example:**

Let’s consider the same table as in the previous example:

A | B | C | |
---|---|---|---|

John | 25 | M | |

Alice | 30 | F | |

Bob | 28 | M | |

Emma | 22 | F | |

David | 35 | M |

To calculate the average age of females (F) from the table, you can use:

`=AVERAGEIFS(B:B, C:C, "F")`

This formula will yield `26`

, as it calculates the average of the ages of females from column B where column C is equal to “F”.

## 70. TRANSPOSE with Array Formula

You can use the TRANSPOSE function with an array formula to switch rows and columns of a range.

**Example:**

Let’s assume cells A1 to A3 contain the values: `1`

, `2`

, and `3`

.

- Select cells B1 to D1.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=TRANSPOSE(A1:A3)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

A | B | C | |
---|---|---|---|

1 | 2 | 3 |

The formula `TRANSPOSE(A1:A3)`

is automatically applied to each cell in the range B1:D1, switching the values from a row into a vertical column.

## 71. UNIQUE with Array Formula

You can use the UNIQUE function with an array formula to get a list of unique values from a range in multiple rows and columns.

**Example:**

Let’s assume cells A1 to B3 contain the following values:

A | B |
---|---|

John | 25 |

Alice | 30 |

John | 28 |

- Select cells C1 to D3.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=UNIQUE(A1:B3)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

C | D |
---|---|

John | 25 |

Alice | 30 |

The formula `UNIQUE(A1:B3)`

is automatically applied to each cell in the range C1:D3, giving a list of unique name and age combinations from the original table.

## 72. FLATTEN with Array Formula

The FLATTEN function can be used with an array formula to convert a range with multiple rows and columns into a single column.

**Example:**

Let’s assume cells A1 to B3 contain the following values:

A | B |
---|---|

John | 25 |

Alice | 30 |

Bob | 28 |

- Select cells C1 to C6.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=FLATTEN(A1:B3)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

C |
---|

John |

Alice |

Bob |

25 |

30 |

28 |

The formula `FLATTEN(A1:B3)`

is automatically applied to each cell in the range C1:C6, converting the range into a single column.

## 73. FILTER with Array Formula

You can use the FILTER function with an array formula to get a filtered range of cells based on multiple conditions.

**Example:**

Let’s assume cells A1 to B5 contain the following values:

A | B |
---|---|

John | 25 |

Alice | 30 |

Bob | 28 |

Emma | 22 |

David | 35 |

To filter the names of people whose age is greater than or equal to 25, you can use:

- Select cells C1 to C5.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=FILTER(A1:A5, B1:B5>=25)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

C |
---|

John |

Alice |

Bob |

David |

The formula `FILTER(A1:A5, B1:B5>=25)`

is automatically applied to each cell in the range C1:C5, filtering the names based on the condition that the age in column B is greater than or equal to 25.

## 74. SORT with Array Formula

You can use the SORT function with an array formula to sort a range based on multiple columns.

**Example:**

Let’s assume cells A1 to C5 contain the following values:

A | B | C |
---|---|---|

John | 25 | M |

Alice | 30 | F |

Bob | 28 | M |

Emma | 22 | F |

David | 35 | M |

To sort the table first by gender (column C) in ascending order, and then by age (column B) in descending order

To achieve this sorting order, we can use the SORT function along with the SEQUENCE function to create a custom array formula.

**Example:**

Let’s assume cells A1 to C5 contain the following values:

A | B | C |
---|---|---|

John | 25 | M |

Alice | 30 | F |

Bob | 28 | M |

Emma | 22 | F |

David | 35 | M |

To sort the table first by gender (column C) in ascending order, and then by age (column B) in descending order, you can use the following array formula:

- Select cells D1 to F5 (or any empty range of the same size).
- Enter the following formula in the formula bar (without pressing Enter yet):

`=SORT(A1:C5, SEQUENCE(ROWS(A1:C5)), 1, C1:C5, 1, B1:B5, -1)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The sorted result will be:

D | E | F |
---|---|---|

Alice | 30 | F |

Emma | 22 | F |

Bob | 28 | M |

David | 35 | M |

John | 25 | M |

The formula `SORT(A1:C5, SEQUENCE(ROWS(A1:C5)), 1, C1:C5, 1, B1:B5, -1)`

is automatically applied to each cell in the range D1:F5, sorting the data first by column C (gender) in ascending order, and then by column B (age) in descending order.

The SEQUENCE function generates a sequence of numbers to help maintain the original order of rows before sorting. By using this custom array formula, you can achieve complex sorting patterns that are not directly supported by the SORT function alone.

## 75. SUBSTITUTE

**Formula:** `=SUBSTITUTE(text, old_text, new_text, [instance])`

The SUBSTITUTE formula replaces occurrences of a specified old_text with new_text in a given text.

**Example:**

Let’s assume cell A1 contains the text `"I love apples, and apples are delicious."`

.

`=SUBSTITUTE(A1, "apples", "bananas")`

will yield `"I love bananas, and bananas are delicious."`

, as it replaces all occurrences of `"apples"`

with `"bananas"`

in the text.

The optional `[instance]`

parameter allows you to specify which occurrence of the old_text you want to replace. For example, `2`

will replace the second occurrence, `3`

will replace the third occurrence, and so on.

## 76. LEN

**Formula:** `=LEN(text)`

The LEN formula calculates the length of a given text (including spaces).

**Example:**

Let’s assume cell A1 contains the text `"Hello, World!"`

.

`=LEN(A1)`

will yield `13`

, as the text has 13 characters, including spaces.

## 77. MID

**Formula:** `=MID(text, start, num_chars)`

The MID formula extracts a substring from a given text, starting from a specified position (start) and including a specified number of characters (num_chars).

**Example:**

Let’s assume cell A1 contains the text `"Hello, World!"`

.

`=MID(A1, 7, 5)`

will yield `"World"`

, as it extracts the substring starting from the 7th character and including the next 5 characters.

## 78. LEFT

**Formula:** `=LEFT(text, num_chars)`

The LEFT formula extracts a specified number of characters from the beginning of a given text.

**Example:**

Let’s assume cell A1 contains the text `"Hello, World!"`

.

`=LEFT(A1, 5)`

will yield `"Hello"`

, as it extracts the first 5 characters from the text.

## 79. RIGHT

**Formula:** `=RIGHT(text, num_chars)`

The RIGHT formula extracts a specified number of characters from the end of a given text.

**Example:**

Let’s assume cell A1 contains the text `"Hello, World!"`

.

`=RIGHT(A1, 6)`

will yield `"World!"`

, as it extracts the last 6 characters from the text.

## 80. TEXT

**Formula:** `=TEXT(value, format_text)`

The TEXT formula formats a numeric value (date, time, number, etc.) based on a specified format.

**Example:**

Let’s assume cell A1 contains the date `2023-07-28`

.

`=TEXT(A1, "MMMM DD, YYYY")`

will yield `"July 28, 2023"`

, as it formats the date in the specified format.

You can use a wide variety of format codes to customize the display of dates, times, and numbers. For example, `"MM/DD/YYYY"`

will display the date as `"07/28/2023"`

.

## 81. CONCAT

**Formula:** `=CONCAT(value1, [value2, ...])`

The CONCAT formula concatenates multiple strings or values into a single text string.

**Example:**

Let’s assume cells A1 and A2 contain the values `"Hello"`

and `"World"`

.

`=CONCAT(A1, " ", A2, "!")`

will yield `"Hello World!"`

, as it concatenates the values in A1 and A2 along with the space and exclamation mark.

## 82. RAND

**Formula:** `=RAND()`

The RAND formula generates a random decimal number between 0 and 1.

**Example:**

`=RAND()`

will yield a random decimal number, such as `0.724314`

.

If you want to generate a random number within a specific range, you can use arithmetic operations. For example, to generate a random integer between 1 and 10, you can use `=INT(RAND()*10)+1`

.

## 83. RANDBETWEEN

**Formula:** `=RANDBETWEEN(bottom, top)`

The RANDBETWEEN formula generates a random integer between two specified values (bottom and top).

**Example:**

`=RANDBETWEEN(1, 10)`

will yield a random integer between 1 and 10, such as `7`

.

## 84. NOW with TIME

The NOW function can be combined with TIME to display the current date and time in a specific format.

**Example:**

`=NOW()`

will yield the current date and time, such as `7/28/2023 11:35:14 AM`

.

To display only the time in 12-hour format, you can use:

`=TIME(HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()))`

This will yield the current time, such as `11:35:14 AM`

.

## 85. COUNTUNIQUE

**Formula:** `=COUNTUNIQUE(range)`

The COUNTUNIQUE formula counts the number of unique values within a specified range.

**Example:**

Let’s assume cells A1 to A5 contain the values `10`

, `20`

, `30`

, `20`

, and `40`

.

`=COUNTUNIQUE(A1:A5)`

will yield `4`

, as there are four unique values (`10`

, `20`

, `30`

, and `40`

) within the range.

## 86. COUNTIFS with OR Logic

You can use the COUNTIFS formula with OR logic to count cells that meet at least one of the specified criteria.

**Example:**

Let’s assume cells A1 to A5 contain the following values:

A |
---|

10 |

20 |

30 |

40 |

50 |

To count the number of cells that contain either `20`

or `30`

, you can use:

`=COUNTIFS(A1:A5, 20) + COUNTIFS(A1:A5, 30)`

This formula will yield `2`

, as there are two cells that contain either `20`

or `30`

.

## 87. SUMIFS with OR Logic

You can use the SUMIFS formula with OR logic to calculate the sum of cells that meet at least one of the specified criteria.

**Example:**

Let’s assume cells A1 to A5 contain the following values:

A |
---|

10 |

20 |

30 |

40 |

50 |

To calculate the sum of cells that contain either `20`

or `30`

, you can use:

`=SUMIFS(A1:A5, A1:A5, 20) + SUMIFS(A1:A5, A1:A5, 30)`

This formula will yield `50`

, as the sum of cells `A2`

and `A3`

is `20 + 30 = 50`

.

## 88. AVERAGEIFS with OR Logic

You can use the AVERAGEIFS formula with OR logic to calculate the average of cells that meet at least one of the specified criteria.

**Example:**

Let’s assume cells A1 to A5 contain the following values:

A |
---|

10 |

20 |

30 |

40 |

50 |

To calculate the average of cells that contain either `20`

or `30`

, you can use:

`=AVERAGEIFS(A1:A5, A1:A5, 20) + AVERAGEIFS(A1:A5, A1:A5, 30)`

This formula will yield `25`

, as the average of cells `A2`

and `A3`

is `(20 + 30) / 2 = 25`

.

## 89. CONCATENATE with Array Formula

You can use the CONCATENATE function with an array formula to concatenate multiple ranges into a single range.

**Example:**

Let’s assume cells A1 to A3 contain the values: `1`

, `2`

, and `3`

, and cells B1 to B3 contain the values: `A`

, `B`

, and `C`

.

- Select cells C1 to C3.
- Enter the following formula in the formula bar (without pressing Enter yet):

`=CONCATENATE(A1:A3, B1:B3)`

- Press
`Ctrl`

+`Shift`

+`Enter`

(or`Cmd`

+`Shift`

+`Enter`

on Mac) to apply the array formula.

The result will be:

A | B | C |
---|---|---|

1 | A | 1A |

2 | B | 2B |

3 | C | 3C |

The formula `CONCATENATE(A1:A3, B1:B3)`

is automatically applied to each cell in the range C1:C3, concatenating the values from columns A and B.

## 90. RANK

**Formula:** `=RANK(value, data, [is_ascending])`

The RANK formula returns the rank of a value in a given data set. You can specify whether the ranking is in ascending or descending order.

**Example:**

Let’s assume cells A1 to A5 contain the following values:

A |
---|

10 |

20 |

30 |

20 |

40 |

To find the rank of `30`

in the data set, you can use:

`=RANK(30, A1:A5)`

This formula will yield `3`

, as `30`

is the third-largest value in the data set.

By default, the `[is_ascending]`

parameter is set to `TRUE`

, meaning the ranking is in ascending order. If you want the ranking in descending order, you can use `=RANK(30, A1:A5, FALSE)`

.

## 91. NESTED ARRAYFORMULA

You can nest an ARRAYFORMULA inside another ARRAYFORMULA to perform calculations on arrays within arrays.

**Example:**

Let’s assume cells A1 to B3 contain the following values:

A | B |
---|---|

1 | 2 |

3 | 4 |

5 | 6 |

To calculate the sum of each row and then the sum of those sums, you can use:

`=SUM(ARRAYFORMULA(ARRAYFORMULA(A1:A3) + ARRAYFORMULA(B1:B3)))`

This formula will yield `21`

, as it first adds the corresponding values in columns A and B to get the row sums (`[3, 7, 11]`

) and then sums those row sums.

## 92. SEARCH

**Formula:** `=SEARCH(find_text, within_text, [start_position])`

The SEARCH formula finds the position of a substring (find_text) within a longer text (within_text) and returns the position as a number.

**Example:**

Let’s assume cell A1 contains the text `"Hello, World!"`

.

`=SEARCH("World", A1)`

will yield `8`

, as the word `"World"`

starts at the 8th position in the text.

The optional `[start_position]`

parameter allows you to specify the position in the text where the search should start. For example, `=SEARCH("o", A1, 5)`

will yield `8`

, as it starts searching from the 5th position and finds the first occurrence of `"o"`

at position 8.

## 93. DATEVALUE

**Formula:** `=DATEVALUE(date_text)`

The DATEVALUE formula converts a date string in a recognized format to a serial number representing that date.

**Example:**

Let’s assume cell A1 contains the date string `"2023-07-28"`

.

`=DATEVALUE(A1)`

will yield the serial number `45012`

, which represents July 28, 2023.

## 94. TIMEVALUE

**Formula:** `=TIMEVALUE(time_text)`

The TIMEVALUE formula converts a time string in a recognized format to a decimal representing that time.

**Example:**

Let’s assume cell A1 contains the time string `"12:30 PM"`

.

Congratulations! You’ve now explored a comprehensive list of 101 formulas that can greatly enhance your productivity and efficiency while working with Google Sheets. These formulas cover a wide range of tasks, from basic arithmetic calculations to complex data manipulations. By mastering these formulas and their applications, you’ll become more proficient in using Google Sheets for various tasks, whether it’s for personal use, academic purposes, or professional projects. So, go ahead and start applying these formulas in your spreadsheets, and unlock the full potential of Google Sheets! Happy spreadsheeting!

## Leave a Reply