There is no LARGEIF function in Google Sheets. So for conditional Large in Google Sheets, you can use the IF function with Large.

You can include single or multiple conditions in Large using the IF logical function.

In the following examples I am not only going to use the IF function in Large but also there are some other interesting combinations.

You May Like: Google Sheets Functions Guide.

Different Formulas to Do Conditional Large in Google Sheets

See the Large function in use without any criteria/conditions.

=large(D2:D11,1)

Result: 890

The formula in cell F2 returns the 1st largest value (max value) in the selected range. To return the 3rd largest value, we can use the below Large formula.

=large(D2:D11,3)

Result: 764

Now let me show you how can one include one condition into the formula.

Single Criteria Large Function in Sheets

Using IF Logical Test in Large

This is a Large + IF combo as an equivalent to a non-existing ‘LARGEIF’ function.

I want to find the largest sales volume in the “North” zone. In this case, I can use the below formula.

=ArrayFormula(large(if(C2:C="North",D2:D),1))

Result: 800

Additionally, I have used the Array Formula as the IF is not an array function and we are referring to an array/range in it. The Array Formula making the IF capable of handling an array.

Using Filter in Large

As I have promised here is one more formula that you can use to find conditional large in Google Sheets. This is purely based on Large and Filter.

=large(filter(D2:D,C2:C="North"),1)

This formula first filters the sales volume for the “North” zone and then use that as the data in Large.

Multiple Criteria Large Function in Sheets

Using IF Logical Test with Multiple Conditions in Large

Here again, the IF function requires an Array Formula to work since multiple criteria are in use. If you want to know more about this, you can check this tutorial – How to Use IF, AND, OR in Array in Google Sheets.

Let me introduce the multiple criteria Large formula below. Please make a special attention to how I have included two conditions in IF.

=ArrayFormula(large(If((C2:C="North")+(C2:C="South")=1,D2:D),1))

The above formula returns the largest sales volume in “North” and “South” zones. How to add one more condition in the above formula?

Here is that IF Large formula with three conditions. Hope you could understand the point.

=ArrayFormula(large(If((C2:C="North")+(C2:C="South")+(C2:C="East")=1,D2:D),1))

But in multiple criteria Large, I highly recommend you to use the below Large + Filter + Regex combo which is very simple to code and read.

Using Filter Regex Combo in Large

Before going to the formula I drag your attention to one of my earlier tutorial regarding the Regex use in Filter – Regexmatch in Filter Criteria in Google Sheets [Examples].

See two examples to conditional large in Google Sheets using the powerful Large, Filter, Regexmatch combination formula.

Largest sales volume from north and south regions.

=LARGE(filter(D2:D,regexmatch(C2:C,"North|South")),1)

Largest sales volume from north, south and east regions.

=LARGE(filter(D2:D,regexmatch(C2:C,"North|South|East")),1)

That’s all about the conditional Large in Google Sheets. Enjoy!

Additional Resources:

1. Sum Large/Max n Values Based on Criteria in Google Sheets.
2. Extract the Largest Date in Each Group in Google Sheets [Array Formula].