How to Count in Excel While Skipping Specific Cells Manually

How-to basics · Updated November 20, 2026

To count numeric values in Excel while excluding chosen cells, subtract the count of those cells from the count of the full range. For example, with values in A2:A20 and cells A5 and A9 to ignore, enter =COUNT(A2:A20)-COUNT(A5,A9). This counts numbers in the range but does not include numbers in A5 or A9. If you mean numbering rows while leaving some rows blank, use a skip marker and the separate method below.

Excel’s COUNT functions do not have a built-in option to click cells to exclude, so the practical approach is to list the exceptions in the formula or mark rows with a helper value. Choose the method that matches what “count” means in your worksheet.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows, with a workbook containing the cells you want to count.
  • The address of the range to count and, if applicable, the addresses of the cells to leave out.
  • For row numbering with gaps, a spare helper column where you can label rows as Count or Skip.

The formulas also work in Excel for Mac and Excel for the web. The formula syntax is the same, although regional settings can require semicolons instead of commas between arguments. If Excel rejects a formula with commas, check your list-separator setting.

Step-by-Step

  1. Decide what you want Excel to count. COUNT includes numeric values, including dates and times, but does not count text or empty cells. COUNTA counts cells that are not empty, including text. If you want a count of numbers and your data is in A2:A20, start with COUNT; if you want a count of filled-in entries, use COUNTA instead. This choice matters: using COUNT on a list of names can correctly return zero, even though the cells contain data.
  2. Identify the full range and the exceptions. Write down the range that normally belongs in the count, then note the exact cells to skip. Make sure each exception is inside that range. For instance, if you are counting A2:A20, A5 and A9 can be subtracted from that count. If an excluded cell is outside the main range, do not subtract it: it was never included in the first place.
  3. Enter a formula that counts numbers and subtracts the excluded numeric cells. With numeric values in A2:A20 and A5 and A9 as the cells to omit, enter =COUNT(A2:A20)-COUNT(A5,A9) in a result cell, such as B1. If A2:A20 contains the numbers 10, 20, 30, 40, 50, 60, 70, 80, and 90, and A5 and A9 contain 40 and 80, the result is 7: there are nine numbers in the range, minus two excluded numbers. COUNT is used on the excluded cells too, so a blank or text value in A5 or A9 does not get wrongly subtracted as a number.
  4. Use COUNTA instead when you mean filled cells, not just numbers. With entries of any type in A2:A20 and A5 and A9 to omit, enter =COUNTA(A2:A20)-COUNTA(A5,A9). If the range has 15 filled cells and both excluded cells are filled, the result is 13. Remember that COUNTA treats a formula returning an empty text string as a nonempty cell; if your data contains formulas that display nothing, that behavior may affect the result.
  5. List only the cells you want counted if the range is small. For a few scattered cells, a noncontiguous list can be simpler than counting a large range and subtracting exceptions. With numeric values in A2, A4, and A7, and all other cells intentionally left out, enter =COUNT(A2,A4,A7). If all three cells contain numbers, the result is 3. For text entries, use COUNTA with the same cell references. This method is easy to audit, but it becomes inconvenient when the list of included cells is long or changes often.
  6. Subtract matching text entries when only certain values count. Suppose A2:A20 contains status labels, and you need the number of cells marked Paid while excluding A5 and A9. Enter =COUNTIF(A2:A20,"Paid")-COUNTIF(A5,"Paid")-COUNTIF(A9,"Paid"). If the full range contains eight Paid entries and both excluded cells say Paid, the result is 6. If an excluded cell contains a different status, its COUNTIF result is zero, so it does not reduce the total. Change Paid to the exact label you need, keeping the quotation marks.
  7. If you mean numbering rows with deliberate gaps, add a marker column. Put Count in column A for rows that should receive a number and Skip for rows that should remain blank. For example, enter Count in A2 and A3, Skip in A4, then Count in A5. In B2, enter =IF(A2="Skip","",COUNTIF($A$2:A2,"Count")) and fill the formula down. The results are 1, 2, blank, and 3. The marker column makes the exceptions visible and lets the numbering update when you change a row from Count to Skip or back.
  8. Check the result against a small manual count. Temporarily inspect the included cells and verify that the formula includes the right values and omits only the intended ones. For a count of numbers, count the numeric entries by hand; for a status count, count matching labels. If the result is unexpectedly low or high, confirm that the range and exception references point to the intended cells before changing the formula.

Common Mistakes

Using COUNT for text. COUNT ignores words, codes stored as text, and other nonnumeric entries. If you want to count filled cells, use COUNTA. If you only want a particular label, COUNTIF is usually the clearer choice.

Subtracting an exception twice. The excluded cells should be part of the main range for a subtract-from-total formula. If you have already narrowed the main range so a cell is absent, subtracting it again makes the answer too small. Also avoid repeating the same exception address in the formula.

Subtracting every exception regardless of its contents. A formula such as =COUNT(A2:A20)-2 assumes exactly two numeric cells should be removed. It can give a wrong answer if one of those cells is blank or text, or if the data changes. Subtracting COUNT of the exception cells is safer when the aim is to exclude numeric values.

Accidentally changing a reference when copying. In the numbering example, $A$2 is fixed as the start of the marker range while A2 expands to A3, A4, and so on. If you remove the dollar signs from the starting reference, it can shift as you fill down. Check the formula in the last row as well as the first.

Confusing a blank display with a truly empty cell. A formula that returns an empty string can look blank but is still a formula. COUNTA may count it, while COUNT will not count it as a number. Choose the count function based on the values you intend to include.

Tips

Keep exception references together in a small, clearly labeled area if the list changes frequently. Then you can see what is being excluded without opening a long formula. For just a couple of exceptions, listing their addresses directly is usually easier to maintain. When there are many exceptions, a helper column with an Include or Skip marker is more readable than a formula containing dozens of cell addresses.

Use descriptive labels for a helper column and apply the same spelling consistently. The marker formula checks the exact text Skip or Count, so an accidental trailing space, such as “Skip ”, will not match. You can copy and paste a marker into other rows to reduce typing differences.

Keep the result formula outside the range it counts. If the result cell sits inside the counted range, the formula may include its own result or create a circular reference, depending on the setup. A separate summary cell above, beside, or on another worksheet avoids that problem.

For a formula you will share with people using different Excel regional settings, remember that argument separators may display differently. Excel normally adjusts separators according to the user’s settings. The formulas in this guide use commas, as in the standard Microsoft 365 Windows setup.

Troubleshooting

The formula returns zero, but the cells look filled. The entries may be text rather than numbers, perhaps because they were imported or contain an apostrophe. Use COUNTA if you intend to count all nonempty entries. If they should be numeric, convert the stored text to numbers before relying on COUNT.

The total is too low. Check that the excluded cells are not listed twice and that they really are inside the counted range. With COUNTA, investigate cells containing formulas that display blank, since they can still be counted. With COUNTIF, check that the spelling and spaces in the criterion match the cell values.

The total is too high. Confirm that each exception is included in the subtraction and that you used COUNT rather than COUNTA if only numbers should be counted. In a marker-column setup, look for rows labeled Count that should say Skip. Also check for numbers stored as text: COUNT will ignore them, but other counting methods may include them.

Excel shows an error when you enter a formula. Make sure the formula begins with an equals sign, that parentheses are balanced, and that text criteria have straight quotation marks. In installations using semicolons as the argument separator, replace the formula’s commas between arguments with semicolons. Do not change commas that are part of a text value.

The numbering formula leaves an unexpected blank. Check that the helper cell says exactly Count or Skip, with no extra spaces, and confirm that you filled the formula down through the row. The formula is designed to show a blank for Skip rows; a typo in the marker may instead produce a number or an incorrect sequence.

Frequently Asked Questions

Can I count cells while skipping blanks?
Yes. COUNT ignores blank cells when counting numeric values. COUNTA ignores truly empty cells when counting entries, but it can count formulas that return an empty string. Choose the function that matches the type of data you want counted.

How do I skip several cells in one formula?
For numeric data, subtract COUNT of all the excluded references, separating each cell with a comma, as in =COUNT(A2:A20)-COUNT(A5,A9,A12). Keep those cells within the main range, and do not list one twice.

Can I make Excel skip cells based on a label?
Yes. For a specific status, use COUNTIF on the full range and subtract COUNTIF for each excluded cell, as shown above. If many cells need to be excluded, a helper column is often easier to maintain than a long formula.

Does this work in Excel for Mac or Excel for the web?
Yes. COUNT, COUNTA, COUNTIF, and IF work in Microsoft 365 for Mac and Excel for the web. The formulas are the same; regional list-separator settings may affect whether Excel expects commas or semicolons.

Can I skip a row but keep the numbering continuous?
Yes. Mark the row Skip in the helper column and use the IF and COUNTIF numbering formula. It leaves that row blank, while later rows continue with the next number rather than leaving a gap in the sequence.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.