This post will guide you how to use Google Sheets DEGREES function with syntax and examples.

## 1. Google Sheets DEGREES Function Description

The Google Sheets DEGREES function converts radians into degrees. And it will return a numeric value. For examples, if you pass the PI() function into the DEGREES function as its argument, and it will return 180.

The purpose of this function is used to convert radians to degrees in google sheet and it’s returned value is the degrees.

The DEGREES function is a build-in function in Google Sheets and it is categorized as a MATH function.

## 2. Google Sheets DEGREES Function Syntax

The syntax of the DEGREES function is as below:

=DEGREES (angle)

Where the DEGREES function argument is:

• angle -This is a required argument. The angle in radians that you want to convert to degrees.

## 3. Google Sheets DEGREES Function Examples

The below examples will show you how to use google sheets DEGREES Function to convert radians into degrees.

1# get the degrees of pi radians, enter the following formula in Cell B1.

``=DEGREES (PI())``

2# get the degrees of 1.25 radians, enter the following formula in Cell B2.

``=DEGREES (1.25)``

This post will guide you how to use Google Sheets MIDB function with syntax and examples.

## 1. Google Sheets MIDB Function Description

The Google Sheets MIDB function returns a substring (a specified number of bytes) starting from the middle of a text string and up to a specified number of bytes.

The MIDB function can be used to extract a given bytes of characters from the middle of a text string in google sheets. It returns a substring from a text string at the position that you specify.

The MIDB function is a build-in function in Google Sheets and it is categorized as a Text Function.

## 2. Google Sheets MIDB Function Syntax

The syntax of the MIDB function is as below:

``= MIDB (text, start_num, length_bytes)``

Where the MIDB function arguments are:

• text -This is a required argument. The text string that you want to extract substring from.
• start_num-This is a required argument.  The position of the first character that you want to extract in text string. The index from the left of text string from which to begin extracting. And the first character in text string is the index 1.
• length_bytes-This is a required argument.  The number of the bytes that you want to extract from a text string.

Note:

• If `start_num`` `is greater than the length of text, the MIDB function will return empty text.
• If `start_num` is less than 1, the MIDB function will return the #VALUE! Error value.
• If `Length_bytesis` negative, MIDB will return the #VALUE!  Error value.
• If you want to extract a substring from start_num to the end of text string, and you can use the LEN function to calculate the length of the text string, you should not specify a large number to `length_bytes` argument for this case.
• If you want to extract a substring beginning with a particular character, and you should use the SEARCH function to get the index of  that character in text string.
• `Length_bytes` must be greater than or equal to 0.
• `Start_num` must be greater than or equal to 1.
• If text string has only single bytes characters and the MIDB function returns the same value as MID function.

## 3. Google Sheets MIDB Function Examples

The below examples will show you how to use google sheets MIDB Text function to extract a substring from a text string.

#1 To extract 10 bytes of substring  from the text string in B1, starting at the 5th character, just using the following formula:

``=MIDB(B1,5,10)  //it returns “le sheets”``

#2 If num_chars value is greater than the length of remaining bytes, and the MID function will return all remining characters:

``=MIDB("google",3,200) //it returns “ogle”``

#3 if text value is a number, and the returned value is also a text by MID function:

``=MIDB(1234,2,2)   //it returns “23”``

Note:

You can use the LEFTB function to extract substring from the left side of a text string in Google Sheets. And you can also use another function called RIGHTB function to extract a substring starting from the right side of the text string. And if you wish to extract a substring starting from the middle position of text string, you can use the MIDB function in google sheets.

## 4. Related Functions

The Google Sheets LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
The Google Sheets LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…

This post will guide you how to use Google Sheets EXP function with syntax and examples.

The EXP function in Google Sheets is used to calculate the value of the exponential function, which is e raised to the power of a given number.

## 1. Google Sheets EXP Function Description

The Google Sheets EXP function returns e raised to the power of a number.  The constant e is equal to 2.71828182845904, and it is the base of the natural logarithm.

You can use EXP function to get the value of e raised to the power of a given number.

The EXP function can be used to find the value of e raised to the power of a number in google sheets. And its returned value is the constant e raised to the power of a given number.

The EXP function is a build-in function in Google Sheets and it is categorized as a MATH function.

## 2. Google Sheets EXP Function Syntax

The syntax of the EXP function is as below:

= EXP (number)

Where the EXP function argument is:

• numberThis is a required argument. The power to raise e to.

Note:

• To calculate powers of other bases, use the exponentiation operator (^).
• EXP is the inverse of LN, the natural logarithm of number.

## 3. Google Sheets EXP Function Examples

The below examples will show you how to use google sheets EXP Function to return value of the constant e raised to a given number.

1# get the approximate value of e, enter the following formula in Cell B1.

``=EXP(1)``

2# get the base of the natural logarithm e raised to the power 2, enter the following formula in Cell B2.

``=EXP(2)``

This post will guide you how to use Google Sheets CHAR function with syntax and examples.

## 1. Google Sheets CHAR Function Description

The Google Sheets CHAR function returns the character specified by a number (ASCII Value) according to the current Unicode table. And you can get the Unicode table online at the Unicode website.

The CHAR function can be used to specify a character that are hard to enter in a formula in google sheets. For example, if you wish to enter a line break, and you can use the CHAR(10) function to add a line break to  a text string in a formula in google sheets.

The CHAR function is a build-in function in Google Sheets and it is categorized as a Text Function.

## 2. Google Sheets CHAR Function Syntax

The syntax of the CHAR function is as below:

``=CHAR(number)``

Where the CHAR function argument is:

Number -This is a required argument. the number value is in decimal format and the number of character to look up from the current Unicode table in decimal format.

Note:

• If you provided invalided number, such as: a string value, you will get an error from the Google Sheets CHAR function, it is like to be the “#VALUE!” error
• The CHAR function only have one argument, which must be an valid number in decimal format. The result from Google Sheets CHAR function is a text value. And the CHAR function can be useful when you want to specify a character in formulas or function that are hard to type directly.
• You need to know that not all Unicode characters can be display properly on your computers and you may need to install or enable special fonts or languages so that it can be displayed properly.

## 3. Google Sheets CHAR Function Example

#1 get a character from number 65, 33,  in the computer’s character set

#2 enter one Double Quotes with text in a formula

If you wish to concatenate double quotes with text in Google sheets, and you can use the CHAR function with a number 34 as its argument, type:

=CHAR(34)

## 4. Conclusion

You need know that the CHAR function will only return a single character. If you want to return a string of characters, you can use the CONCATENATE function to combine multiple CHAR functions.

This article will introduce you how to use the COUNTIF function in google sheets, and will show you a good example to better understand the usage of the COUNTIF function.

## Google Sheets COUNTIF Function Description

The google sheets COUNTIF function is used to count the number of cells in a cell range that meet a given condition. This function can be used to count cells which contain different types of values, such as numbers, dates, empty cells, non-empty cells or cells containing some special characters.

The COUNTIF function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

You can use a logical operator in the COUNTIF function, such as: >,>=,<,<=<>,=, and the function also supports wildcards characters (*, ?) .

## Google Sheets COUNTIF Function Syntax

The syntax of the Google Sheets COUNTIF function is explained as follows:

`= COUNTIF (range, criteria)`

Where the arguments of the COUNTIF function are as follows:

• Range – This is a mandatory option, you want to use to apply the given conditions to count the number of matching cells in the specified cell range.
• Criteria – This is a mandatory option, it used to define which cells will be counted

## Google Sheets COUNTIF Function Examples

The below examples will show you how to use google sheets COUNTIF function to count the number of cells that match a given criteria in a cell range.

Example 1: If you want to count the number of cells that contain the “excel” text value in cell range B1:B4, then you can use the following COUNTIF formula：

`=COUNTIF(A1:A4,"excel")`

You can see that the above COUNTIF formula returns a result of 2, which shows that B1: B4 range contains two “excel” text value of the cell.

This article will introduce you how to use the RANK function in google sheets, and will show you a good example to better understand the usage of the RANK function.

## Google Sheets Rank Function Description

The Google Sheets Rank function is used to return the ranking of a given number from a list. If there are duplicate numbers in the list, then the numbers will be in the same rank.

The RANK function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Google Sheets Rank Function Syntax

The syntax of the Google Sheets Rank function is explained as follows:

`= RANK (number,reference ,[order])`

Where the arguments of the RANK function are as follows:

• Number – This is a required option, the number that you would like to get ranked
• reference – This is a mandatory option, it may be an array or cell region, or a cell reference contains the specified number
• Order – This is an optional, you can specify the number of 0 or 1 to determine the type of sorting; if the value of order is 1, it means that the RANK function will be sorted in ascending order, otherwise it will be ranked in descending order.

## Google Sheets RANK Function Examples

The below examples will show you how to use Google Sheets RANK Function to get the rank of a given value within a supplied range of cells.

Example 1: If you want to get the sorting of number 2 in a given list B1:B4 in google sheets, then you can use the RANK function to do so with the following formula:

`=RANK(2,B1:B4,1)`

Note: The number 2 must be a value that exists in B1:B4, otherwise google sheets will report the error “Rank has no valid input data“.

This post will guide you how to use Google Sheets MAX function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.

The MAX function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the MAX function is as below:

`= MAX(num1,[num2,…numn])`

Where the MAX function arguments are:

• num1 -This is a required argument. A number, or a reference to a number or a range that contains numeric values
• Num2,…Numn – This is an optional argument.  A number, or a reference to a number or a range that contains numeric values

Note:

• The num1 arguments can be numbers, arrays, defined names or references that contain numeric values.
• IF the arguments contains non numeric value, it returns 0.
• The value returned by the MAX function is still a numeric value.
• If the argument is an array or reference, only numbers in that array are used. Empty string, logical values or text of numbers are ignored.

## Google Sheets MAX Function Examples

The below examples will show you how to use Google Sheets MAX Function to get the largest value form the numbers in a range.

#1 To get the largest value from a range B1:B4, just using the following Google Sheets formula:

`=MAX(B1:B4)`

This post will guide you how to use Google Sheets SUMIFS function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. You can use the SUMIFS function to sum cells that based on the dates, numbers or texts based on one criteria in Google Sheets. And it also supports logical operators and wildcards.

The SUMIFS function will extend the capabilities of the SUM function by giving you a choice that you want only sum those values that meet a single or multiple criteria.

The SUMIFS function is a build-in function in Google Sheets and it is categorized as a Math and Trigonometry Function.

## Syntax

The syntax of the SUMIFS function is as below:

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

Where the SUMIFS function arguments are:

• sum_range -This is a required argument. The actual cells to sum together.
• criteria_range1 -This is a required argument. The range of cells that you want to apply criteria1 against.
• criteria1 – This is a required argument. The criteria to use on criteria_range1.so this criteria defines which cells in criteria_range1 will be applied.
• [criteria_range2, criteria2] -This is an optional argument. It will be specified additional ranges and their associated criteria. Criteria2 will be applied against criteria_range2 and so on. It can be up to 127 criteria.

Note:

• Any criteria that include logical or math symbols must be enclosed in double quotation marks.
• The numeric criteria can be supplied without quotation marks.
• You can use the wildcard characters in the criteria argument.
• You can use multiple operators in the criteria argument.

### Google Sheets SUMIFS Function Examples

The below examples will show you how to use Google Sheets SUMIFS Function to sum the values in a range of cells based on multiple criteria.

Suppose that you have a sales table as shown below:

 Name Product Region Sales Tom Excel North \$210 Tom Word North \$230 Tom Access North \$190 Jenny Excel North \$150 Jenny Word North \$240 Jeff Excel East \$140 Jeff Word East \$340

1# to sum of the sales of the product “Excel” in region “North“, enter the following formula in Cell E1.

`=SUMIFS(D2:D8,B2:B8,"Excel",C2:C8,"North")`

This post will guide you how to use Google Sheets AVERAGEIFS function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.

The AVERAGEIFS function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the AVERAGEIFS function is as below:

`= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)`

Where the AVERAGEIFS function arguments are:

• Average_range – This is a required argument. The range of cells that you want to average.
• Criteria_range1  – This is a required argument. The range to apply the associated criteria.
• Criteria – This is an optional argument. The criteria used to define which cells are averaged.

### Google Sheets AVERAGEIFS function Example

The below examples will show you how to use Google Sheets AVERAGEIFS Function to return the average of all numbers in a range of cells based on multiple criteria.

#1 To get the average of all numbers in average range B1:B5 and meet the criteria that equal to “2015” in range A1:A5, and equal to “TRUE”in range C1:C5 , just using the following Google Sheets formula:

`=AVERAGEIFS(B1:B5,A1:A5,2018,C1:C5,"TRUE")`

This post will guide you how to use Google Sheets AVERAGEIF function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets AVERAGEIF function returns the average of all numbers in a range of cells that meet a given criteria.

The AVERAGEIF function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the AVERAGEIF function is as below:

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

Where the AVERAGEIF function arguments are:

• `Range `-This is a required argument.  The range of cells that you want to apply the criteria to average
• `Criteria `– This is a required argument.  The criteria used to define which cells are averaged
• `Average_range`This is an optional argument.  The average range is actually to average. If it is omitted, the function will use Range values to average.

## Google Sheets AVERAGEIF Function Example

The below examples will show you how to use Google Sheets AVERAGEIF Function to return the average of all numbers in a range of cells based on a given criteria.

#1 To get the average of all numbers in average range B1:B5 and meet the criteria that equal to “2022” in range A1:A5, and the, just using the following Google Sheets formula:

`=AVERAGEIF(A1:A5,2022,B1:B5)`

This post will guide you how to use Google Sheets SUBTOTAL function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets SUBTOTAL function returns the subtotal of the numbers in a list or database.

The SUBTOTAL function is a build-in function in Google Sheets and it is categorized as a Math and Trigonometry Function.

## Syntax

The syntax of the SUBTOTAL function is as below:

`= SUBTOTAL(function_num, ref1, [ref2])`

Where the SUBTOTAL function argument is:

• Function_num – This is a required argument.  It can be set as 1-11 or 101-111 for the subtotal.  1-11 that includes hidden rows and 101-111 excludes hidden rows.
 Function_num (includes hidden values) Function_num (ignores hidden values) Function 1 101 AVERAGE 2 102 COUNT 3 103 COUNTA 4 104 MAX 5 105 MIN 6 106 PRODUCT 7 107 STDEV 8 108 STDEVP 9 109 SUM 10 110 VAR 11 111 VARP

Ref1– This is a required argument. The first named range or reference that you want to subtotal.

## Google Sheets SUBTOTAL Function Example

the below examples will show you how to use Google Sheets SUBTOTAL function to return the subtotal of the numbers in a list.

#1 =SUBTOTAL(103,B:B)

Note: the above formula will call COUNTA function to count the number of cells(B:B) that contain numbers. It will return value 5.

#2 =SUBTOTAL(109,B:B)

Note: the above Google Sheets formula will call SUM function to add all numbers in range cell B:B, so it will return value: 160.

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

This post will guide you how to use Google Sheets MIN function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.

The MIN function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the MIN function is as below:

= MIN(num1,[num2,…numn])

Where the MIN function arguments are:

• num1 -This is a required argument. A number, or a reference to a number or a range that contains numeric values
• Num2,…Numn – This is an optional argument.  A number, or a reference to a number or a range that contains numeric values

Note:

• The MIN function will not compare empty cells
• The MIN function can accept up to 255 arguments with numeric value.
• The MIN function will ignore Boolean values (TRUE and FALSE)
• The MIN function will ignore numbers that the format is text.
• The returned value of The MIN function is a numeric value.

## Google Sheets MIN Function Examples

The below examples will show you how to use Google Sheets MIN Function to get the smallest value form the numbers.

#1 To get the smallest value from the column A, just using the following Google Sheets formula:

`=MIN(B:B)`

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

This post will guide you how to use Google Sheets LARGE function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. It also can be used to get the nth largest value from a range of cells or an array.

For example, the LARGE function can find the first, second, third or nth smallest value in a list or an array.

The LARGE function is a build-in function in Google Sheets  and it is categorized as a Statistical Function.

## Syntax

The syntax of the LARGE function is as below:

`= LARGE (array,nth)`

Where the LARGE function arguments are:

• `array `-This is a required argument.  An Array or a range of cells that contains numeric values from which you want to get the nth largest value.
• `nth `– This is a required argument.  The position of the number that you want to return

Note:

• If the first argument is empty, then the LARGE function returns #NUM! Error.
• If nth is less than or equal to 0 or if nth is greater than the number of values in array, the LARGE function returns the #NUM! Error.
• If nth is the number of values in array, then LARGE (array,1) returns the largest value, and LARGE(array,n) returns the smallest value.

## Google Sheets LARGE Function Examples

The below examples will show you how to use Google Sheets LARGE Function to get the largest value form the numbers in a range.

#1 To get the largest value from a range B1:B4, just using the following Google Sheets formula:

`=LARGE(B1:B4,1)`

The above formula returns the largest number from a range B1:B4, it returns 113.

2# To get the second largest value from the Range B1:B4, you can use the following LARGE function:

`=LARGE(B1:B4,2)`

The second argument nth in the above LARGE function is 2, so it will fetch the second largest function in the range B1:B4. It returns 34.

3# To get the largest value from the range B1:B4 that contains blank cells, enter into the following formula in the Cell C1:

`=LARGE(B1:B4,1)`

The LARGE function will ignore blank cells in the Range. You will see that the Cell B3 is a blank cell, and it is ignored by the LARGE function. The largest value is still returned as 113.

4# To get the largest value from the range B1:B5 that contains text string, specific character and logical values. Using the following LARGE formula:

`=LARGE(B1:B5,1)`

The LARGE function will ignore values in the range B1:B5 that contain text string, specific character and logical value.

5# To get the largest value from the range B1:B5 that contain an error, using the below formula:

`=LARGE(B1:B5,1)`

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets

This post will guide you how to use Google Sheets COUNTA function with syntax and examples in Google Spreadsheet.

## Description

The Google Sheets COUNTA function counts the number of cells that are not empty in a range. And it returns the number of non-blank cells within a range or values. It can be used to count all the cells in a range that contain numbers, text, logical values, error values and empty text string.

The COUNTA function is a build-in function in Google Spreadsheet and it is categorized as a Statistical Function.

## Syntax

The syntax of the COUNTA function is as below:

`= COUNTA(value1, [value2],…)`

Where the COUNTA function arguments are:

• `value1 `-This is a required argument.  The first item, cell reference, or range within which you want to count numbers.
• `Value2 `– This is an optional argument. You can enter up to 255 value arguments.

Note:

• The COUNTA function does not count empty cells.
• If you want to only count cells that contain numbers, just use the COUNT function.
• If you want to count logical values, text, or error values, you can use the COUNTA function.

### Google Sheets COUNTA Function Examples

The below examples will show you how to use Google Sheets COUNTA Function to count the number of cells in a range that are not empty.

#1 To count the number of cells in the range B1:B4, just using the following excel formula:

`=COUNTA(B1:B4)`

This post will guide you how to use Google Sheets MOD function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Google Sheets. And the returned result will have the same sign as divisor.

The MOD function is a build-in function in Google Sheets and it is categorized as a Math and Trigonometry Function.

## Syntax

The syntax of the MOD function is as below:

`=MOD (number, divisor)`

Where the MOD function arguments are:

• `number` – This is a required argument.The number that you want to get the remainder.
• `divisor` – This is a required argument. The number that you want to divide number.

Note:

• If the divisor is 0, then the MOD function will return the #DIV/0! Error.
• The MOD function returns a result in the same sign as the divisor.

## Google Sheets MOD Function Examples

The below examples will show you how to use Google Sheets MOD Function to get the remainder after number is divided by divisor.

1# to get the remainder of 6/5, enter the following formula in Cell B1.

`=MOD(6,5)`

2# 1# to get the remainder of -6/5, enter the following formula in Cell B1.

`=MOD(-6,5)`

This post will guide you how to use Google Sheets COUNT function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range.

The COUNT function is a build-in function in Google Spreadsheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the COUNT function is as below:

`= COUNT(value1, [value2],…)`

Where the COUNT function arguments are:

• `value1 `-This is a required argument.  The first item, cell reference, or range within which you want to count numbers.
• `Value2 `– This is an optional argument. You can enter up to 255 value arguments.

Note:

• Arguments that are numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as “1”) are counted.
• If the argument is an array or reference, only numbers in that array are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.
• You can use the COUNTA function to count logical values, text, error values.
• You can use the COUNTIF function to count only numbers that meet certain criteria.

## Google Sheets COUNT Function Examples

The below examples will show you how to use Google Sheets COUNT Function to count the number of cells that contain numbers in a range.

#1 To count the number of cells in the range B1:B4, just using the following Google Sheets formula:

`=COUNT(B1:B4)`

### Related Functions

The Google Sheets CHOOSE function returns a value from a list of values based on index. The syntax of the CHOOSE function is as below:=CHOOSE (index_num, value1,[value2],…) …

This post will guide you how to use Google Sheets AVERAGE function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets AVERAGE function returns the average of the numbers that you provided.

The AVERAGE function is a build-in function in Google Sheets and it is categorized as a Statistical Function.

## Syntax

The syntax of the AVERAGE function is as below:

`= AVERAGE (number1,[number2],…)`

Where the AVERAGE function arguments are:

• `Number1 `-This is a required argument.  the first numeric values
• `Number2 `– This is an optional argument.

## Google Sheets AVERAGE Function Example

The below examples will show you how to use Google Sheets AVERAGE Function to return the average of the numbers provided.

#1 To get the average value of the numbers in cell range B1:B3 Cell, just using the following Google Sheets formula:

`=AVERAGE(B1:B3)`

This post will teach you how to use Google Sheets COUNTIFS function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets COUNTIFS function returns the count of cells in a range that meet one or more criteria. It allows to use the criteria with the numeric value or text string, which may be contain the wildcards in text-related criteria, and also support to use the logical operator to build an expression statement for criteria. You also should know that the COUNTIFS function is not case-sensitive.

The COUNTIFS function is a build-in function in Google Sheets and it is categorized as a Math Function.

## Syntax

The syntax of the COUNTIFS function is as below:

`= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)`

Where the COUNTIFS function arguments are:

• `criteria_range1`This is a required argument.  the first range in which to apply the associated criteria.
• `criteria1` – This is a required argument.  the first critiria to use on criteria_range1
• `criteria_range2, criteria2`, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Note:

• Each range’s criteria is applied one cell at a time. If all of the first cells meet their associated criteria, the count increases by 1. If all of the second cells meet their associated criteria, the count increases by 1 again, and so on until all of the cells are evaluated.
• If the criteria argument is a reference to an empty cell, the COUNTIFS function treats the empty cell as a 0 value.
• You can use the wildcard characters— the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character, and an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

## Google Sheets COUNTIFS Function Examples

The below examples will show you how to use Google Sheets COUNTIFS Function to count cells that match multiple criteria.

#1 To count how many rows have numbers that are greater than 50 in range C2:C6, and also the product name is Google Sheets in range A2:A6 , just using the following Google Sheets formula:

`=COUNTIFS(A2:A6,"=excel",C2:C6,">4")`

This post will guide you how to use Google Sheets SUMPRODUCT function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. And it returns a numeric value.

The SUMPRODUCT function is a build-in function in Google Spreadsheets and it is categorized as a Array Function.

## Syntax

The syntax of the SUMPRODUCT function is as below:

`= SUMPRODUCT (array1,[array2],…)`

Where the SUMPRODUCT function arguments are:

• `array1 `-This is a required argument. The first array or range that you want to multiply and then add.
• `[array2],`– This is an optional argument.  The second array or range that you want to multiply and then add.

Note:

• The array arguments must have the same dimensions, or it will return the #VALUE! Error.
• The SUMPRODUCT treats non-numeric items in the arrays as zeros.

## Google Sheets SUMPRODUCT Function Example

The below examples will show you how to use Google Sheets SUMPRODUCT Function to return the sum of products.

#1 To get the total amount of all products in the range B1:B5, just using the following Google Sheets formula:

`=SUMPRODUCT(C2:C5,D2:D5)`

2# The arrays or ranges must have the same dimensions, otherwise returns the #VALUE error. See the below picture:

3# non-numeric items in the arrays or ranges are treated as zeros.

This post will guide you how to use Google Sheets IF function with syntax and examples in Google Spreadsheets.

## Description

The Google Sheets IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE.

The IF function is a build-in function in Google Spreadsheets and it is categorized as a Logical Function.

## Syntax

The syntax of the IF function is as below:

`= IF (condition, [true_value], [false_value])`

Where the IF function arguments are:

• `Condition` -This is a required argument.  A user-defined condition that is to be tested.
• `True_value` – This is an optional argument.  The value that is returned if condition evaluates to TRUE.
• `False_value` – This is an optional argument.  The value that is returned if condition evaluates to FALSE.

The below examples will show you how to use Google Sheets IF Function to return one value if the condition is TRUE or FALSE.

`=IF(B1>90, "good", "bad")`

Note: the above Google Sheets formula will test a condition “b1>90”, if the condition is true, then “good” will return or “bad” will return.

## Nested IF statements

The Google Sheets IF function just only test one condition and if you want to deal with more than one condition and return different actions depending on the result of the tests,  then you need to include several IF statements (functions) in one Google Sheets IF formula, these multiple IF statements are also called Google Sheets Nested IF formula(Nested IFs).

For example:

`=IF(A1>=80, "excellent", IF(A1>=60, "good", IF(A1>0, "bad", "no valid score")))`

When you are writing an If statement, you may need to use any of the following Google Sheets logical operators:

 Operator Meaning Example Description = Equal To A1=B1 Returns True if a value in cell A1 is equal to the values in cell B1; FALSE if they are not. > Greater Than A1>B1 Returns True if a value in cell A1 is greater than the values in cell B1; FALSE if they are not. >= Greater Than or Equal to A1>=B1 Returns True if a value in cell A1 is greater than or equal to the values in cell B1; FALSE if they are not. < Less Than A1 Not Equal to A1<>B1 Returns True if a value in cell A1 is not equal to the values in cell B1; FALSE if they are not.

Let’s see some examples for logical operators in Google Sheets formula:

 A1 B1 8 5
```  =(A1>B1)  //Output: TRUE

=(A1<B1) //Output: FALSE

=(A1>=B1) //Output: TRUE

=(A1<>8) //Output: FALSE

=(A1<>B1) //Output: TRUE

=(IF A1>3, “TRUE”, “FALSE”) //Output: TRUE```

Question1: I want to write an IF Formula based on the below test criteria in the Google Spreadsheets.

If the number in Cell B1 is greater than 30, then I want it to return A

If the number in cell B1 is between 20 and 30, then I want it to return B

If the number in Cell B1 is below 20, then I want it to return C

The below formula is what I currently write:

`=IF(B1>30,"A",IF(B1<30,B1>20,"B","C"))`

When I entered the above formula in the Cell C1, and it will return an Error.

Answer:  You can use IF function in combination with AND function to reflect the above logic. So you can do it using the following formula:

`=IF(B1>30,"A",IF(AND(B1>=20,B1<=30),"B","C"))`

Or you can use another IF formula without AND function as follows:

`=IF(B1>30,"A",IF(B1<20,"C","B"))`

Question 2: I want to write a Google Sheets Formula for sales rep, If the sales are less than 10K, then the member will get no commission. If the sales are between 10 and 50K, then the member will get a 3% commission. If the sales are more than 50k, then the member will get 5% commission. Could you help me?

Answer: Based on the above description, we can use the following Google Sheets IF formula:

`=IF(B1<10,0,IF(B1<50,B1*3%,B1*5%))`

Question 3: I want to select students for the scholarship in school, and based on the student’s scores and attendance value, if the student scores more than 85 and has the attendance of more than 85%, then he/she will get the scholarship. Please help me how to write this formula based on my test criteria.

Answer: you can use the IF function with the AND function to check whether both of two conditions are met or not. If the test are met, then return “Yes”, otherwise returns “No”.

So you can use the following IF formula in Google Sheets:

`=IF(AND(B1>85, C1>85%),"Yes","No")`

Question 4:  I am trying to write an Google Sheets formula based on the following logic:

I want to enter a formula in Cell C1 that will Add B1, B2 and B3 or multiply B1 by 1, multiply B2 by 2, multiply B2 by 3, which action will be taken based on what you put in Cell D1.

Appreciated for any help. Thanks

Answer: Based on the above logic description, you need to use SUM function to count the sum of B1, B2 and B3. We can use the following IF formula:

`=IF(D1="Add”,SUM(B1,B2,B3),IF(D1="Multiply",B1*B2*B3,"ERROR"))`

Question 5:  I am trying to write a formula in Google Sheets to check the employee number field and returns the relevant band of employee. I already have one IF formula, but I always return the first value of “A”. Could you help?

`=IF(B1>=10,"A",IF(B1>=20,"B",IF(B1>=50,"C")))`

Answer:  when you write an IF nested formula, you need to start with the largest number first or start the smallest number first with less than or equal to operator.

1# start with the largest number first, we can write down the below formula:

`=IF(B1>=50,"C",IF(B1>=20,"B",IF(B1>=10,"A")))`

2# start with the smallest number first, you need to change the >= to <= , just see the below formula:

`=IF(B1<=10,"A",IF(B1<=20,"B",IF(B1<=50,"C")))`

Question 6: I want to write a Formula in Google Sheets to return “bad” if the cell B1 is either <100 or >500, otherwise, it should be returned the value of cell B1.

Answer: For the above logic test, we should use the OR function in the IF condition, so we can write down the below IF formula:

`=IF(OR(B1>500,B1<100),"good",B1)`

Question 7: I want to write a formula in Google Sheets using IF function to check if the cell B1 >10 and B1<20. If TRUE, returns “good”, If FALSE, returns “bad”

Answer: You can use IF function in combination with AND function to check the value of Cell B1, if cell B1 is greater than 10 and cell B1 is less than 20, then returns “good”, otherwise, it will return “bad”.

`=IF(AND(B1>10,B1<20),"good","bad")`

Question 8:  I need to write a nested IF statement in Google Sheets to check the following logic:

If Cell B1 is less than 5, then multiply it by 5.

If Cell B1 is greater than or equal to 5 but less than 10, then multiply it by 10.

If cell B1 is greater than 10, then multiply it by 15.

Answer: This is a generic nested IF formula in Google Sheets, we can write the below nested IF statement to reflect the logic.

`=IF(B1<5, B1*5, IF(B1<10, B1*10, B1*15))`

Question 9:  I want to write a formula to match the following logic in Google Sheets:

If A1*B1 <=10, then returns A

If A1*B1 >10 but A1*B1 <=20, then returns B

If A1*B1 >20 but A1*B1 <=30, then returns C

If A1*B1>30, then returns D

Answer: You can use IF function to build a Nested IF statement in combination with AND function to achieve it. Let’s write down the following IF formula:

`=IF(A1*B1<=10,"A", IF(AND(A1*B1>10,A1*B1<=20),"B", IF(AND(A1*B1>20,a1*B1<=30,"C","D"))))`

Question 10: I want to write an IF function to check if the value in cell B1 is blank or Text string or Numeric value, if the cell B1 is empty, then return “blank”, if the cell B1 is a text string, then return “Text”, if the value in cell B1 is a numeric value, then return “number”. Any help for this formula, thanks.

Answer: Based on the description, you need to use ISBLANK function to check if the value in cell b1 is blank or not. And need to use ISTEXT function to check if the value in cell B1 is a text string or not and need to use ISNUMBER function to check if the value in cell B1 is a number or not. So you can write a nested IF statements in combination with ISBLANK, ISTEXT and ISNUMBER functions in Google Sheets as follows:

`=IF(ISBLANK(B1),"blank", IF(ISTEXT(B1),"Text", IF(ISNUMBER(B1),"number")))`

Question 11:  I want to write a formula in Google Sheets to calculate the bonus for employees in company, if the employee salary is greater than or equal to \$2000, then the bonus will be 10% of the salary , otherwise, the bonus will be 5% of the employee salary.

Answer:  we need to check if the salary in Cell B1 (it’s the salary of the first employee) is greater than or equal to 2000. It the condition test is TRUE, then returns B1*10%, otherwise returns b1*5%. So we can write down the following IF formula in Google Sheets:

`=IF(B1>=2000, B1*10%, B1*5%)`

Question 12: In Google Spreadsheets, I want to create an IF function to check if any employee who have at least 10 years of experience and whose salary is greater than \$5000, If TRUE, then the bonus will be 20% of salary.

Answer: you can use IF function in combination with AND function to check if the value in cell B1 is greater than or equal to 10 and the value in Cell C1 is greater than 5000. If both of two conditions are TRUE, then returns C1*20%, otherwise returns “No Bonus”.

Let’s write down the following IF statement as follows:

`=IF(AND(B1>=10,C1>5000),C1*20%, "No Bonus")`

Question 13: I want to create an IF formula in Google Sheets to check the following text logic:

If B1<10, then multiply B1 by 1%, but the returned value is not less than 50.

If B1>10, then multiply B1 by 2%, but the returned value is not greater than 100.

Answer: To reflect the first test condition, you need to use MAX function to match the condition. To reflect the second test condition, you need to use MIN function to match that the returned value should not be greater than 100. So we can use the following Google Sheets IF formula:

`=IF(B1<10, MAX(50,B1*1%), IF(B1>10, MIN(100,B1*5%)))`

Question 14:  I want to use IF function to check if B1 is greater than 10 and B2 is greater than 20 and B3 is less than 30, if TRUE, then returns “good”, otherwise, it should be returned “bad”.  So How to create the IF formula based on the above test criteria to check three conditions at the same time.

Answer: you need to use AND function within the IF function in Google Sheets to create an IF formula as follows:

`=IF(AND(B1>10,B2>20,B3<30),"good","bad")`

Question 15:  I have an IF formula that might cause a division by zero error, I don’t know how to avoid this kind of errors in Google Sheets formula.

Answer: You can use ISERROR function to catch this kind of errors then use the IF function to check the returned values from ISERROR function to avoid an error. So we can write an IF function in combination with ISERROR function in Google Sheets. For example, we can use the following IF formula:

`=IF(ISERROR(B1/C1),0, B1/C1)`

The ISERROR function will return TRUE when trying to divide B1 by 0.

Question 16:  I need to create a formula in Google Sheets to reflect the following logic:

If B1=sheets, then return W

If B1=info, then return A

Answer:  You can use IF function to create a nested IF statements as follows:

`=IF(B1=" Google ","G",IF(B1=" sheets ","S",IF(B1=" info ","I")))`

Question 17:  I have a worksheet that containing cells that are formatted as date format. I want to write an IF formula to check the first value in Cell (month part). So I am trying to create the following IF formula:

`=IF(LEFT(B1,1)=8, "August","Null")`

The returned results are always “Null”.

Answer: As the dates are not recognized as string, so you cannot use the LEFT function to exact the first value in the dates. At this moment, you need to use Month function to convert the date to its month number in Google Sheets. So we can write down the below IF formula in combination with Month function:

`=IF(MONTH(B1)=8, "August","Null")`

Question18: I am trying to create an IF formula to check if the time value in cells are greater than 10.00h for the following date format: 08/11/2018 09:43.50.  Appreciative of any help.

Answer: In Google Sheets, you can use HOUR, MINUTE, SECOND functions to compare the date or time value. so if you want to compare the value in Cell B1 if it is greater than 10h, then you can use HOUR function within IF function, just like this: HOUR(B1)>10, so we can write down the below formula:

`=IF(HOUR(B1)>10, "greater","less")`

Question 19: I want to create an IF function and need to combine with another RAND function.  The formula just works find for the first IF_VALUE_TRUE statement, but the formula works not good for IF_VALUE_FALSE statement. Here is the IF formula I have got:

`=IF(ISBLANK(B1),"","=rand()")`

In the above IF function, it will return empty string when the value in cell B1 is blank. Otherwise, it should add a random function, the problem is that the cell doesn’t run the RAND function. So how can I fix it?  Thanks

Answer:  when you write an IF formula, if you want to enclose others function, you need not to add quotes around the RAND () function, so just remove the quotes and equal sign in your IF function. Just like the below IF formula:

`=IF(ISBLANK(B1),"",RAND())`

Question 20: I am trying to write an IF formula in Google Sheets to reflect the following logic:

If B1 is greater than or equal to 50 and C1 is 0

OR

If B1 is greater than or equal to 30 and C1 is greater than or equal to 1

OR

If B1 is greater than or equal to 20 and C1 is 2

Then take the following action:  D2/E2

Otherwise, return FALSE

I have wrote the following IF formula, but it doesn’t work at all.

`=IF(AND(B1>=50,C1>=0),OR(AND(B1>=30,C1>=1)),OR(AND(B1>=20,C1>=2)),D2/E2,"FALSE")`

Answer: you need to nest your different AND function within an OR function in the IF formula. So you can try the below IF formula:

`=IF(OR(AND(B1>=50,C1>=0),AND(B1>=30,C1>=1),AND(B1>=20,C1>=2)), D2/E2,"FALSE")`

Question 21: I want to create an IF function in combination with MID function in Google Sheets. it need to check if the value in one specified cell is TRUE, then return the first six characters from another cell. Otherwise return empty value.

Answer: you just need to add MID function within IF function in Google Sheets, and do not add any quotes around MID function.    So you can use the following IF formula to achieve your request:

`=IF(B1=TRUE, MID(C1,1,5), "")`

Question 22:  I have a Google Sheets as below:

A     B

——

20   O

30   V

10   T

50   T

I want to create an IF formula to reflect the following logic:

IF cell A1 is less than or equal to 30 and Cell B1 is equal to “O” or “V”, if TRUE, then returns 300, otherwise returns 400.

IF Cell A1 is greater than 30 and Cell B1 is equal to “O” or “V”, then returns 500, otherwise returns 600.

I have wrote the below tow IF formulas for the above two conditions as follows:

```=IF(AND(A1<=30,OR(B1="O",B1="V")),300,400)

=IF(AND(A1>30,OR(B1="O",B1="V")),500,600)```

I am able to check the above two IF formula and the returned results is OK… But I am not able to combine the above two IF formula into a single IF formula. So anybody can help? Many thanks

Answer:  Based on the above logic, you can use the below IF formula to combine with above two IF formulas:

`=IF(A1<=30,IF(OR(B1="O",B1="V"),300,400),IF(OR(B1="O",B1="V"),500,600))`

Question 23: I am trying to write an IF function to prevent zero and negative values in cells. What I would like is that if the value in cell B1 is less than or equal to 0, then it should be returned “Null” otherwise, it should return the calculation of Cells value, like as:B2*(C2-D2)*E2.

The below IF formula is what I have:

`=IF(B1<=0,"Null","B2*(C2-D2)*E2")`

When I run the formula above, it only returns my calculation string and do not take the actual calculation.

Answer: In Google Sheets, the double quotes make any values in between be recognized as Text string. So if you want to take calculation for your IF formula, just remove the double quotes.  Let’s see the modified IF formula as follows:

`=IF(B1<=0,"Null",B2*(C2-D2)*E2)`

Question 24: I want to create an new IF function to check if the value in cells is Saturday or Sunday, If TRUE, returns “yes”, otherwise, returns “No”. And I am using the following IF formula, but I get an error, so what’s wrong for this formula?

`=IF((OR(\$B1="Saturday","\$B1="Sunday"),"yes","no"))`

Answer: you need to use the WEEKDAY function within IF function to handle the dates if they are in date format, like as: 11/8/2018.

So you can use the below IF function to achieve your logic:

`=IF(WEEKDAY(\$B\$1,2)>5,"yes","no")`

You can also use TEXT function within the IF function to achieve the same results, just like the below IF formula:

`=IF(LEFT(TEXT(\$B\$1,"ddd"))="S","yes","no")`

Question 25: I want to write an IF function in Google Sheets to check if the first character in one Cell is equal to 5, then the returned value should be the five rightmost characters of that cell, otherwise, the returned value should be the four rightmost characters. I wrote one IF formula as follows, but it doesn’t work.

`=IF(LEFT(B1,1)=5, RIGHT(B1,5),RIGHT(B1,4))`

In the above IF function, it always return the rightmost four characters even though the first character in Cell B1 starts with “S”.  Please help me to fix it.

Answer: you should know the returned value of the LEFT function firstly in Google Sheets. As the LEFT function will return a Text value, so you also need to provide a string for comparison, so adding quotes to enclose it. Just like the below IF function:

`=IF(LEFT(B1,1)="5", RIGHT(B1,5),RIGHT(B1,4))`

There is another way to achieve the same results, you can use NUMBERVALUE function to convert the result of LEFT function to a numeric value, like the below IF function:

`=IF(NUMBERVALUE(LEFT(B1,1))=5,RIGHT(B1,5),RIGHT(B1,4))`

Question 26:  I have 2 columns contain date and time or just only contain time. And I want to check if the times of column A is greater than the times of column B. the key issue may be that column A has the date and time.  I wrote the following IF formula to run it in Cell C1, but it returned the inaccurate results.

`=IF(A1>B1,"yes","no")`

Any help would be appreciated… Many thanks!

Answer:  the date part of the value in column A is the integer, while the time is the decimal. You can use the following IF formula:

`=IF(A1-INT(A1)>B2),"yes","no")`

Question 27:  The below are the results that I expected, and I want cell B1 to B4 can detect the string from A1 to A4 automatically and return the same string value plus the severity level when the test match. For example, If A1 is equal to “critical”, then it should be returned “critical severity 1” in the cell B1. Etc.

And I am using the following IF formula, but it does not work at all. Please help to fix it. Thanks

`=IF(A1="Critical","Critical Severity 1",""),IF(A1="High","High Severity 2",""),IF(A1="Medium","Medium Severity 3",""),IF(A1="Low","Low Severity 4","")`

 A B critical critical severity 1 high high severity 2 medium Medium severity 3 low low severity 4

Answer: you can try to run the following IF formula in Google Sheets:

`=IF(A1="critical","critical Severity 1",IF(A1="high","high Severity 2",IF(A1="medium","medium Severity 3",IF(A1="low","low Severity 4",""))))`

Question 28: I am working on a Google Sheets file and want to create a new IF formula to reflect the following logic:

If the value in Cell A1 is equal to the value in Cell A2, then check if the minus of B1 and B2 is equal to a special value, and if the condition is TRUE, returns “yes”, otherwise, returns “no”.  Here is the IF formula I have:

`=if(A1=A2,B1-B2=5 or B1-B2=-5 or B1-B2=20 or B1-B2=-20, "yes", "no")`

Any help is appreciated…Thanks

Answer: you need to use OR function with IF function to create a nested IF statement to achieve your request. So you can try the following Google Sheets IF formula in your Google Sheets file.

`=IF(A1=A2,IF(OR(B1-B2=5,B1-B2=-5,B1-B2=20,B1-B2=-20),"yes","no"),"no")`

Question 29: I want to create an IF formula to check the range of cells in Google Sheets.  I have scores of different subject for a student, and want to check if any one of scores is less than 60, If TRUE, then return “BAD”.  Can this logic be done with the IF statements in Google Sheets?

Answer: you can use COUNTIF function within IF function to create a generic IF formula as follows:

`=IF(COUNTIF(B:B,"<60")>0,"BAD","Good")`

Question 30: I am trying to create a new IF statement so that when the formula is looking at Row A and Row B, the returned values should be shown in the Row C. the following logic need to be checked:

IF the value in the Row A is equal to “NA” And the value in the Row B is equal to “NA”, then return “NA” value in Row C.

IF the value in the Row A is equal to “NA”, and the value in the Row B is equal to “denied”, then return “denied” value in Row C.

IF the value in the Row A is equal to “allowed” and the value in the Row B is equal to “NA”, then return “allowed” value in Row C.

Here is my formula:

`=IF(AND(A1 = "NA", B1 = "NA"),"NA",IF(OR(A1="denied",B1 ="denied"),"denied", "NA"))`

I don’t know how to include “allowed” in the IF formula above, anyone can help this, many thanks.

Answer: This is a typical nested if statement, you can use OR function within IF function in Google Sheets. We can write down this nested IF formula as follows:

`=IF(OR(A1="denied", B1="denied"), "denied", IF(OR(A1="allowed", B1="allowed"), "allowed", "NA"))`