How to Use a LARGE IF Formula in Google Sheets

To use a LARGE IF formula in Google Sheets, you'll need to combine the LARGE and IF functions to find the nth largest value that meets a specific condition. The LARGE function returns the nth largest value in a dataset, and the IF function evaluates a condition and returns one value if the condition is true and another if it's false.

Here's how to use the LARGE IF formula in Google Sheets:

1. Open a new or existing Google Sheets document.
2. Prepare your dataset. Make sure you have a dataset with values and conditions.
3. In an empty cell where you want the result, enter the formula using the following syntax:=LARGE(IF(condition_range = condition, values_range), n)
   • condition_range: The range of cells containing the condition you want to evaluate.
   • condition: The specific condition you want the IF function to check.
   • values_range: The range of cells containing the values you want to apply the LARGE function to.
   • n: The position of the largest value you want to return (e.g., 1 for the largest, 2 for the second largest, and so on).
4. Press Enter to apply the formula.

Example

Let's say you have a dataset of sales made by different sales reps, and you want to find the 2nd largest sale made by a specific sales rep. Here's an example dataset:

To find the 2nd largest sale made by John, follow these steps:

1. In an empty cell (e.g., C1), enter the following formula:=LARGE(IF(A2:A6="John", B2:B6), 2)
   • A2:A6 is the condition_range containing the sales rep names.
   • "John" is the condition we want to evaluate.
   • B2:B6 is the values_range containing the sales amounts.
   • 2 is the position of the largest value we want to return (2nd largest).
2. Press Enter to apply the formula. The result in this example should be 2000, which is the 2nd largest sale made by John.