google sheets countifs - Free Excel Tutorial

May 23, 2022May 23, 2022

Rank Based on Specific Value in google sheets

This article will guide you how to sort the values in a given column based on a specific value in another column in google sheets. How to sort a range of values in google sheets by using IF function and COUNTIFS function based on a specific value in another column.

Rank Based on Specific Value in google sheets1

Rank Based on Specific Value

Suppose you have a list with two columns of data, where the first column is a list of products and another column is the number of sales. If you have an urgent need to quickly sort the number of sales for a given product name, you can’t solve this request with the RANK function, because it can only be used to sort values up to one range. So is there a quick and easy way for sorting in google sheets? Of course yes, the following will show how to use the IF function in combination with COUNTIFS function to build a new google sheets formula to solve the problem. The formula is as follows.

For example, if you want to rank the sales values in the range B2:B6 based on the specified product name “excel” in the range A2:A6, then you can use the following formula to get the ranking results：

=IF(A2="excel",COUNTIFS(A$2:A$6,"excel",B$2:B$6,">"&B2)+1,"")

Next, you need to type the above formula in another blank cell and press Enter on your keyboard to apply the formula, and after that you need to drag the autofill handler down to the other cells to apply this formula.

Rank Based on Specific Value in google sheets1

As you can see from the example above, the products in column A, “excel“, can now be ranked by their sales.

Let’s See How This Formula Works

=COUNTIFS(A$2:A$6,”excel”)

This formula is used to count the number of excel product names in a given cell range A2:A6.

Rank Based on Specific Value in google sheets1

=COUNTIFS(B$2:B$6,”>”&B2)

This formula is used to count the number of times that the value in the given cell range B2:B6 is greater than the current sales value.

Rank Based on Specific Value in google sheets1

=COUNTIFS(A$2:A$6,”excel”,B$2:B$6,”>”&B2)

The formula is used to count the number of products with sales greater than the current product sales, and must meet one additional condition is that the product name must be excel.

Rank Based on Specific Value in google sheets5

=COUNTIFS(A$2:A$6,”excel”,B$2:B$6,”>”&B2)+1

The returned result of the above formula adding the number 1 is the actual ranking of the specified product in its own product list.

Rank Based on Specific Value in google sheets1

Related Functions

Google Sheets IF function
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 Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Google Sheets COUNTIFS function
The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

April 15, 2022April 16, 2022

Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time in google sheets.

So read this article till the end, and let’s dive into it;

Average cells based on multiple criteria in google sheets1

General Formula in google sheets

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria in google sheets.

AVERAGEIFS: In google sheets, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
Comma symbol (,): It acts as a separator that helps to separate a list of values.
Parenthesis (): The main function of this symbol is to group the elements.
Range: It represents the input value from your worksheet.
Criteria: The specific criteria or values used to calculate the average.

Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria in google sheets. In the example, the formula in H2 is as follows:

=AVERAGEIFS(sales,product,E2,region,F2)

Average cells based on multiple criteria in google sheets1

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2^nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

=AVERAGEIFS(sales,product,E2,sales,F2)

Average cells based on multiple criteria in google sheets1

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)

Average cells based on multiple criteria in google sheets1

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3^rd example as follows:

Consider the example below to calculate the average of numbers based on multiple criteria.
The image below will show the input values for Columns B, C, and D.
Next, enter the given formula in the formula bar section.
Finally, we’ll see the result in cell H3.

Summary

This article explains how to use the formulas in google sheets to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

Related Functions

Google Sheets AVERAGEIFS function
The Google Sheets AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
google Sheets COUNTIFS function
The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
Google Sheets SUMIFS Function
The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

March 21, 2022March 21, 2022

Count Cells that are Case Sensitive in Google Sheets

If you are a frequent user of Google Spreadsheets, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out that it is the most inefficient way to do it, particularly when Google Spreadsheets has the versatility to do it in a matter of seconds. As a result of attentively reading this article, you will learn about the many adding or counting case-sensitive cells.

counting the cells that are case sensitive in google sheets1

Simple generic formula:

=SUMPRODUCT(--ISNUMBER(FIND(value, range)))

Summary

You may use a formula depending on the ISNUMBER and FIND functions and the SUMPRODUCT function to calculate the number of cells that contain specified text (i.e., hold a substring), taking into consideration upper and lower case. D1 in the example displayed has the following formula, which has been written down:

=SUMPRODUCT(--ISNUMBER(FIND(C1,$A$1:$A$6)))

Let’s See How This Formula Works

In this example, the purpose is to count the number of times each code appears as a substring in a case-sensitive manner, as shown below. When calculating text values, the functions COUNTIF and COUNTIFS are excellent choices. However, since these functions are not case-sensitive, they cannot be utilized to address this issue. To check for substrings, the FIND function should be used in conjunction with the ISNUMBER function, and the SUMPRODUCT function should be used to sum up, the results.

= FIND(C1,$A$1:$A$6)

The FIND function is case-sensitive and accepts three parameters: find text, within the text, and start num. It is also case-sensitive and takes three arguments. Here is the text we are searching for, and this is the text that we are searching inside. Start num is the number of characters to begin searching in find text. Because start num defaults to 1, we are not supplying a number in this instance. This is necessary since we always want FIND to begin with the first character. When finding the text is discovered inside within text, FIND returns the position of the found text as a number: When finding the text is located within text, FIND returns the position of the found text as a number:

counting the cells that are case sensitive in google sheets1

Note: When the find text function fails to yield a result, FIND returns the #VALUE! Error.

=ISNUMBER(FIND(C1,$A$1:$A$6))

This implies that we may be using the ISNUMBER function to transform the output of the FIND function into a TRUE or FALSE value using the ISNUMBER function. A TRUE value will arise from any number, while a FALSE value will result from any error:

As demonstrated in the example, we have four substrings in column C and a variety of codes in columns A1:A6, representing the data for the designated range. Specifically, we want to know how many times each substring in C1:C4 occurs in A1:A6, and we want to know how many times each substring is case-sensitive.

Related Functions

Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Google Sheets FIND function
The Google Sheets FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Google Sheets SUMPRODUCT function
The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Google Sheets COUNTIF function
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],…) …
Google Sheets COUNTIFS function
The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…

March 15, 2022March 15, 2022

Google Sheets COUNTIFS Function

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")

March 14, 2022March 15, 2022

sort dates in chronological order in google sheets

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, google sheets’s Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular format, such as mm/dd/yyy, and then arrange them chronologically. This post will demonstrate how to sort dates in chronological order and how to check if a range of dates are in chronological order.

Explanation

This is an excellent illustration of how the SUMPRODUCT function may be used to overcome the inability of the COUNTIF or COUNTIFS functions to operate. In this scenario, the purpose is to verify that all dates within a certain range are in chronological order and to display a “YES” value only when dates are in chronological order.

The argument is pretty straightforward, although maybe not apparent. Rather of ensuring that all dates are bigger than the previous one, we determine if any prior date is greater than the next. If we discover even one, the dates will be out of sequence. They are if we discover none (zero).

At its heart, this formula uses the SUMPRODUCT function to determine the number of dates in A1:C1 that are larger than or equal to B1:D1:

=SUMPRODUCT(--(A1:C1>=B1:D1))

Due to the fact that we are employing the larger than or equal to (>=) operator, successive duplicate dates will be disallowed.

You can use the following formula based on the IF function, the SUMPRODUCT function, type:

=IF(SUMPRODUCT(--(A1:C1>=B1:D1)) =0,”YES”,””)

Related Functions

Google Sheets SUMPRODUCT function
The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Google Sheets COUNTIF function
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],…) …
Google Sheets IF function
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 Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Google Sheets COUNTIFS function
The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…