How to Calculate a Percentage With COUNTIF in Excel

To get a COUNTIF percentage in Excel, count the cells that match, divide by the total number of cells, and format the result as a percentage: =COUNTIF(C2:C9,"Pass")/COUNTA(C2:C9). That one pattern answers questions like "what share passed?", "what percent said Yes?" and "how many orders were late, as a percentage?". This guide walks through it on a small table, then covers groups, blanks and the errors that trip people up.

The COUNTIF percentage formula#

A percentage is a part divided by a whole. COUNTIF gives you the part, and COUNTA gives you the whole:

=COUNTIF(range, criteria)/COUNTA(range)

• COUNTIF counts the cells that match your condition, such as "Pass". See the complete guide to COUNTIF for every kind of criteria.
• COUNTA counts every cell that isn't empty, so it gives the total number of entries.

All the examples use this table of test results in A1:D9:

How to calculate the pass rate step by step#

To find the share of candidates who passed:

1. Click an empty cell, for example F2.
2. Type =COUNTIF(C2:C9,"Pass"). On its own, this returns 5.
3. Type /COUNTA(C2:C9). COUNTA returns 8.
4. Press Enter. The cell shows 0.625.
5. Format it as a percentage: press Ctrl+Shift+% (the same keys work on a Mac), or click % in the Home tab. It now shows 63%.

The full formula is:

=COUNTIF(C2:C9,"Pass")/COUNTA(C2:C9)

Why 63% and not 62.5%? The Percentage button shows no decimal places, so Excel rounds the display. The cell still holds 0.625. Click Increase Decimal once to see 62.5%.

The fail rate works the same way: =COUNTIF(C2:C9,"Fail")/COUNTA(C2:C9) returns 0.375, or 37.5%. The two always add up to 100%.

How to show the result as a percentage#

Keep the value as a decimal and let the format do the work. These are the three common ways to display it:

The most common mistake is multiplying by 100 and applying the Percentage format. The format already multiplies by 100 for display, so 62.5 becomes 6250%. Pick one: either multiply by 100 and leave the cell as a plain number, or divide only and format as %. The second is better, because other formulas can use the value directly. The guide to the Excel percentage formula explains how Excel stores percentages.

How to find the percentage of values above a number#

Put the comparison inside the COUNTIF criteria, in quotes. To find the share of candidates who scored 75 or more:

=COUNTIF(D2:D9,">=75")/COUNT(D2:D9)

Three scores are 75 or higher (82, 77 and 91), out of 8, so the result is 37.5%. Here COUNT is the better total, because it counts only numbers. That keeps a stray text note like "absent" out of the total.

To compare against a cell, join the operator with &. If G1 holds the target score:

=COUNTIF(D2:D9,">="&G1)/COUNT(D2:D9)

How to calculate a percentage within a group#

Use COUNTIFS for the part and COUNTIF for the group total. To find the pass rate in Sales only:

=COUNTIFS(B2:B9,"Sales",C2:C9,"Pass")/COUNTIF(B2:B9,"Sales")

Sales has three candidates and two passed, so the result is 66.7%. For Finance it is 33.3% (1 of 3), and for HR it is 100% (2 of 2).

Be clear about which question you're answering:

• "What share of Sales candidates passed?" Divide by the number of Sales candidates: 2 ÷ 3 = 66.7%.
• "What share of all passes came from Sales?" Divide by all passes: 2 ÷ 5 = 40%.

Both are valid, and they give very different numbers. The second kind is a share of a total, explained in how to calculate a percentage of total.

How to handle blank cells and #DIV/0!#

COUNTA ignores truly empty cells, so a candidate who hasn't taken the test yet doesn't drag the rate down. If C10 were blank, =COUNTIF(C2:C10,"Pass")/COUNTA(C2:C10) would still return 62.5%. If you divided by ROWS(C2:C10) instead, the blank row would count, and you'd get 5 ÷ 9 = 55.6%.

Two cautions:

• Cells that look empty may not be. A cell with a space, or a formula that returns "", is counted by COUNTA. The article on COUNTIF for non-blank cells shows how to count only cells with real content.
• An empty column gives #DIV/0!. If COUNTA returns 0, the division fails. Wrap the formula: =IFERROR(COUNTIF(C2:C9,"Pass")/COUNTA(C2:C9),0).

Common COUNTIF percentage mistakes#

Check these when the percentage looks wrong:

1. Wrong total range. The two ranges should cover the same rows. COUNTIF(C2:C9,…)/COUNTA(C2:C20) divides by too many cells.
2. Including the header. COUNTA(C1:C9) counts the word "Result" too, giving 5 ÷ 9 instead of 5 ÷ 8.
3. Extra spaces in the data. "Pass " with a trailing space doesn't match "Pass". Clean the column with TRIM.
4. Spelling variants. "Passed" and "Pass" are different words. Use a wildcard, "Pass*", to catch both.
5. Yes/No columns. The same pattern works: =COUNTIF(B2:B20,"Yes")/COUNTA(B2:B20). See how to count Yes in Excel for more Yes/No tricks.

The COUNTIF function guide and the COUNTA function guide are quick syntax references.

Practise it#

COUNTIF percentages combine two skills that job tests check all the time: counting with a condition and formatting a result as a percentage. Practise COUNTIF in a live spreadsheet, then practise percentages and percent change. Each lesson checks your formula instantly. Then try the exercise on this page and work out the pass rate yourself.