COUNTA and COUNTIF in Excel: How to Use Them Together

To find what share of filled status cells say Complete, with statuses in B2:B11, use =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). If five cells say Complete, three contain another status, and two are blank, the result is 5 divided by 8, or 62.5%. COUNTIF counts the matches; COUNTA supplies the number of nonblank entries to use as the denominator.

This combination is useful when you want a count of matching entries alongside a meaningful rate, such as the percentage of completed tasks, approved requests, or qualifying responses. The formula assumes the range contains one kind of item and that every nonblank cell belongs in the denominator. Set up the range and decide what should count as a valid entry before you enter the formula.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows, or a recent Excel version with COUNTA and COUNTIF. Both functions are also available in Excel for Mac and Excel for the web.
A column or row of records to assess, with a consistent value in each cell, such as a task status in B2:B11.
A clear match condition, for example the word Complete, and a decision about whether blank cells should be excluded from the rate.

Step-by-Step

Arrange the data in one consistent range. Put one record in each row and the field you want to assess in a single column. For example, use B2:B11 for ten task statuses, with values such as Complete, In progress, and Waiting. Keep the column heading outside the range. Avoid including totals or notes in the same range, because COUNTA treats any nonempty cell as an entry.
Decide what the denominator should represent. If you want the share of statuses that are Complete among statuses actually entered, use the nonblank cells as the denominator. COUNTA counts cells containing text, numbers, dates, errors, or formulas, and it ignores genuinely empty cells. If your question is instead “What share of all ten planned rows are complete?”, blank rows may need to count in the denominator; in that case, dividing by the fixed number of planned rows is a different calculation.
Choose the exact criterion for COUNTIF. Decide what should qualify as a match. For a status list, the criterion could be Complete; for a category list, it might be North. COUNTIF checks each cell against the condition and returns the number that match. Text matching is not case-sensitive, so Complete and COMPLETE are treated as the same criterion. Extra spaces, however, can prevent a cell from matching.
Select the cell where you want the rate. Click an empty cell outside the data range, such as D2. A separate result cell keeps the calculation from being counted as part of its own source data. Add a nearby label such as Completion rate so readers know what the result means.
Enter the combined formula. With task statuses in B2:B11, where five entries are Complete, three have another status, and two are blank, enter =COUNTIF(B2:B11,"Complete")/COUNTA(B2:B11). COUNTIF returns 5, and COUNTA returns 8, so Excel calculates 5/8, which is 0.625. The formula counts matching statuses first and divides by the nonblank status count; it does not count blank cells as completed.
Format the answer as a percentage. Select the result cell and choose Home > Number > Percentage. Excel displays 0.625 as 62.5% when one decimal place is shown. You can use the increase or decrease decimal buttons in the Number group to adjust the displayed precision. Formatting changes how the result appears, not the underlying calculation.
Check the numerator and denominator separately. Before relying on the percentage, confirm that the number of matching entries and the number of filled entries are what you expect. In this example, five Complete statuses out of eight filled statuses should produce 62.5%. If the displayed result differs, inspect the range boundaries, spelling, and blanks before changing the formula.
Make the criterion easy to change, if needed. You can put the condition in a separate cell, such as D1, and refer to it in the formula. With statuses in B2:B11 and the criterion Complete in D1, enter =COUNTIF(B2:B11,D1)/COUNTA(B2:B11). It returns the same 62.5% for the example above. Changing D1 to In progress updates the rate without editing the formula. Keep the criterion cell outside B2:B11 so it does not become part of the counted data.

The same idea works for other text conditions. With product regions in A2:A20 and a region called North, COUNTIF can count the North entries while COUNTA counts all entered regions. If you use a criterion such as a word followed by an asterisk, COUNTIF can match text that starts with that word. Use such partial matches only when that broader definition is what you intend; otherwise, a specific criterion or a criterion cell is easier to audit.

Common Mistakes

Using COUNTA as if it counted only valid records. COUNTA counts every nonempty cell, including a note, an error value, or a formula that displays an empty string. It does not know which entries are meaningful. If the range contains headings or comments, move them outside the data range or use a narrower range.
Including blank rows without considering their meaning. Truly empty cells are excluded by COUNTA, which is why the example gives a rate among completed entries. But a cell containing a formula that returns an empty string is not truly empty to COUNTA, so it can still be included in the denominator. Decide whether formula-generated blanks represent records before choosing this method.
Expecting COUNTIF to distinguish letter case. COUNTIF is case-insensitive for text. It will count Complete and COMPLETE together. If capitalization itself carries meaning in your data, this basic COUNTIF approach will not separate those values.
Using mismatched ranges. The numerator and denominator should normally refer to the same field and the same rows. If COUNTIF checks B2:B11 while COUNTA checks B2:B12, the extra row can distort the rate. Compare both references carefully, particularly after copying a formula.
Confusing a rate with a raw count. COUNTIF alone returns the number of matches. Dividing it by COUNTA converts that number into a proportion. If you only need to know how many entries match, do not divide; if you need a percentage, format the quotient as a percentage.
Entering a text criterion incorrectly. A literal text criterion must be inside straight double quotation marks in the formula. When a criterion comes from a cell, refer to that cell without quotation marks around the reference. Curly quotation marks copied from a document may cause a formula error.

Tips

Use a descriptive label beside the result and state what the denominator represents, such as “Complete out of entered statuses.” A percentage without that context can be misleading, especially when some records are blank or not yet reviewed.
Convert a growing list into an Excel table using Insert > Table. Tables make it easier to include new records consistently, although structured-reference formulas use different references from the simple cell-range examples here. Check that added rows are included in the calculation.
Keep the condition in a separate cell when you will compare several categories. You can copy the calculation for different criteria and change the criterion cell, which reduces repeated edits inside formulas.
Use consistent data entry. A dropdown list created with Data > Data Validation can help prevent variations such as In progress, In-progress, and In Progress from being entered as separate labels. COUNTIF is not case-sensitive, but spelling and spaces still matter.
When interpreting the result, remember that a percentage is a proportion of the cells COUNTA considers nonempty. It is not automatically a percentage of all people, orders, or planned tasks unless the selected range accurately represents that population.

Troubleshooting

The result is 0% even though matching entries are visible: Check whether the criterion text has leading or trailing spaces, and confirm that the referenced range covers the visible entries. If the criterion is stored in a cell, check that the cell contains the expected value. You can also click an entry and inspect its contents in the formula bar.
The percentage is higher or lower than expected: Count the matching cells and the nonblank cells manually in a small sample. Confirm that both parts of the formula use the same range. Check whether the denominator includes formulas returning empty text, notes, or other content that you did not intend to treat as a record.
Excel displays a decimal instead of a percent: The calculation may be correct; the result cell is simply formatted as a number or General. Select it and choose Home > Number > Percentage. A result of 0.625 should display as 62.5% with one decimal place.
You see #DIV/0!: COUNTA returned zero, meaning the selected range has no cells it considers nonempty. Check that the range points to the data, not an empty column or an incorrectly selected set of rows. If an empty data range is possible, decide how you want the report to behave when there are no entries rather than interpreting a division error as a meaningful rate.
The formula appears as text or Excel rejects it: Make sure the cell is not formatted as Text, that the formula begins with an equals sign, and that the quotation marks around a literal criterion are straight. If Excel uses a different regional list separator on your installation, it may require semicolons instead of commas; the examples here use standard comma-separated Excel syntax.
A formula-generated blank seems to affect the denominator: COUNTA counts a cell containing a formula even when that formula displays an empty string. If those cells should not count as completed entries, adjust the data setup or use a denominator designed for the actual definition of a valid record. Do not assume a visually blank cell is truly empty.

Frequently Asked Questions

Can I use COUNTA and COUNTIF in one Excel formula?
Yes. A common pattern is to divide COUNTIF’s matching count by COUNTA’s nonblank count. That returns the fraction of entered cells meeting the condition. Format the result as a percentage if that is the clearest way to present it.

Does COUNTA count cells that contain formulas?
Yes. COUNTA counts a cell containing a formula even if the formula displays an empty string. It also counts cells with text, numbers, dates, and errors. It ignores cells that are genuinely empty.

Does COUNTIF distinguish uppercase and lowercase text?
No. COUNTIF text comparisons are not case-sensitive. For example, a criterion of Complete matches Complete and COMPLETE. It can still be affected by different spelling or extra spaces.

How do I count matches instead of calculating a percentage?
Use COUNTIF by itself when you only want the number of matching entries. The combined calculation divides that count by COUNTA, so it answers a different question: what share of the nonblank entries match?

Should blank cells be included in the denominator?
That depends on the question. COUNTA excludes genuinely blank cells, giving a rate among entries that have been filled in. If each planned row should count even when its status is blank, use the total number of planned rows as the denominator instead and state that definition clearly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.