Skip to main content

Kutools for Office ā€” One Suite. Five Tools. Get More Done.

How to count ignoring zeros or blank cells in Excel?

Author Sun Last modified

Supposing you have a range of data, which includes several zero values and blank cells as below screenshot shown, and your job is to count cells ignoring both the zero values and blank cells, how can you quickly count them correctly?

Range of data with several zero values and blank cells
Count ignoring zeros and blank cells with formula
Count ignoring zeros and blank cells with Kutools for Excel
Count ignoring blank cells only with Status bar

arrow blue right bubble Count ignoring zeros and blank cells with formula

Here is an arrow formula can help you to count cells ignoring zeros and blank cells.

Select a blank cell that you want to put the counting result, and type this formula =COUNT(IF(A1:E5<>0, A1:E5)) into it, press Shift + Ctrl + Enter key to get the result.

Formula entered in the result cell
Arrow
Formula result displayed

Tip: In the formula, A1:E5 is the cell range you want to count ignoring both blank cells and zero values.

arrow blue right bubble Count ignoring zeros and blank cells with Kutools for Excel

If you want to count cells excluding zeros values and blank cells and then select them, you can apply Kutools for Excelā€™s Select Specific Cells to quickly handle it.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After free installing Kutools for Excel, please do as below:

1. Select the cells you want to count, and click Kutools > Select > Select Specific Cells. See screenshot:
Select Specific Cells option on the Kutools tab on the ribbon

2. In the Select Specific Cells dialog, check Cell under Selection type section, and select Does not equal criterion from the first drop down list under Specific type section, then type 0 into the next text box. See screenshot:
Select Specific Cells dialog

3. Click Ok or Apply, now a dialog pops out to remind you how many cells are selected, and at the meanwhile, all the cells excluding zero values and empty cells have been selected.
Success dialog

Click here to know more about Select Specific Cells.

arrow blue right bubble Count ignoring blank cells only with Status bar

If you want count cells only ignores blank cells, you can select the cells and then view the counting result in the Status bar.

Select the cells you only want to count cells excluding blank cells, and then go to the bottom-right corner of Status bar to view the counting result. See screenshot:
Non-blank cells count on the status bar

Best Office Productivity Tools

šŸ¤– Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functionsā€¦
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language ā€“ supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite ā€” Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license ā€” set up in minutes (MSI-ready)
  • Works better together ā€” streamlined productivity across Office apps
  • 30-day full-featured trial ā€” no registration, no credit card
  • Best value ā€” save vs buying individual add-in