With the help of the PERCENTIF function, we can get the percentage of a range that meets a specific condition in Google Sheets.

Can we use multiple conditions in the PERCENTIF?

Yep! In this function, we may require to follow a workaround to use multiple conditions that involve Sum and Curly Braces. We will go to that later on.

You should use this function carefully to avoid seeing unwanted percentages as output because of blank cells in the range.

Further, you should know the scenario in which you can use it.

For example, using the PERCENTIF function, we won’t be able to calculate the percentage of marks in a test.

For that, you may want to divide the sum of scored marks by the maximum marks of the test and multiply it by 100.

If I have scored 556 (in 6 tests) out of 600 (maximum 100 for each test), the percentage of marks scored will be 556/600*100.

We can use the following formula in Google Sheets if the marks are in A1:A6.

=sum(A1:A6)/600*100

Assume you have a list that contains the marks of a student in 6 subjects who has scored >90 in 4 subjects.

To calculate the percentage of marks >90 in the range (list), we can use 4/6*100. We can solve this using the new PERCENTIF function in Google Sheets.

As a side note, earlier, we were suing COUNTIF to solve it, and here is that formula if the marks are in the range A1:A6.

=countif(A1:A6,">90")/6*100

Syntax and Arguments

Syntax: PERCENTIF(range, criterion)

Range – The cell range to test against a given criterion/condition.

Examples:- A2:A10, A2:B100, {E7:E8,G7:G8}, etc.

Criterion – It’s the condition to test.

Notes:-

The range in the PERCENTIF function may be text or numbers. Depending on that, we may require to use the criterion.

If it’s text, the criterion must be specified within double quotes. Also, we can use wildcard characters such as an asterisk, question mark, and tilde.

If the range is formatted as numbers, specify the criterion with or without using double-quotes. Also, you are permitted to use comparison operators such as >, >=, <, and <=.

Can we use the Operator functions such as GT, LT, etc. in the PERCENTIF function in Google Sheets?

Yep! We will discuss that also in the example section below.

Examples of the PERCENTIF Function in Google Sheets

Sample Data (Numeric Range):

In the above example, I have numbers in the range B2:B10.

The percentage of the numeric range B2:B10 that meets the criterion >90 is 22.22%. Here is my recommended formula.

=PERCENTIF(B2:B10,">"&90)

You can see it in cell D2, where I have combined the comparison operator with the criterion number. The D4 formula, i.e., =PERCENTIF(B2:B10,">90"), will also work.

In the above examples, we can replace the comparison operator > with the function GT as below. But I’m not recommending it.

=ArrayFormula(percentif(GT(B2:B10,90),true))

Finally, here is an alternative formula to the PERCENTIF function in Google Sheets.

=to_percent(countifs(B2:B10,">"&90)/counta(B2:B10))

Sample Data (Text Range):

The percentage of the text range A2:A19 that meets the criterion “Supermarket” is 22.22%.

=PERCENTIF(A2:A19,"Supermarket")

When I want to include any text that contains “Supermarket”, I can use the asterisk wildcards as below.

=PERCENTIF(A2:A19,"*Supermarket*")

Skipping Blank Cells in PERCENTIF in Google Sheets

As you can see, I have used closed ranges in the above formulas like B2:B10 and A2:A19.

Why don’t we use open ranges such as B2:B or A2:A instead?

Please see the vivid red highlighted texts above.

Actually, we are using the PERCENTIF function to get the percentage of a range that meets a specific condition.

If you want to skip blank rows/cells in the range, you should filter it using the FILTER function as below.

=PERCENTIF(filter(A2:A,A2:A<>""),"Supermarket")

It has one issue, though, i.e., associated with cells that contain error values.

If you have error values in the range, such as a #N/A from a Vlookup, the FILTER will omit that error values along with the blanks.

So the percentage you get with a closed and filtered range won’t be the same.

Using Multiple Conditions in PERCENTIF in Google Sheets

We can follow the COUNTIFS OR way to use multiple criteria in the PERCENTIF function in Google Sheets.

The method is to create an array using CURLY BRACES that contains criteria and use the ARRAYFORMULA function to get multiple percentages.

Then use SUM to get the total. Here is one self-explanatory example.

=ArrayFormula(SUM(percentif(A2:A19,{"Supermarket","Fuel"})))

That’s all. Thanks for the stay. Enjoy!

Related Resources

1. How to Calculate Reverse Percentage in Google Sheets.
2. Percent Distribution of Grand Total in Google Sheets Query.
3. Percentage Change Array Formula in Google Sheets.
4. Query to Filter a Column Contains Percentage Values in Google Sheets.
5. Calculating the Percentage between Dates in Google Sheets.
6. How to Round Percentage Values in Google Sheets.
7. How to Get the Top N Percent Scores From Each Group in Google Sheets.
8. Calculating the Percentage of Total in Google Sheets [How To].
9. How to Calculate Percentage Difference In Google Sheets.
10. How to Limit a Percentage Value Between 0 and 100 in Google Sheets.
11. Calculating Percentile for Each Group in Google Sheets.
12. How to Use Percentage Value in Logical IF in Google Sheets.