Skip to main content

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

How to count and sum only positive/negative numbers in Excel?

Author Xiaoyang Last modified

Summing or counting data in a worksheet is typically straightforward, but sometimes you may need to specifically target positive or negative numbers. Are there efficient methods to achieve this? This guide will explore practical solutions to help you selectively sum or count only the positive or negative numbers in your data set.

A screenshot of an Excel dataset containing both positive and negative numbers

Count only positive or negative numbers in a range with formulas

Here, the COUNTIF function can help you quickly count how many positive numbers or negative numbers are present in a range.

1. To count only the positive values, in a blank cell, please enter this formula =COUNTIF($A$1:$D$7,">0") into it, see screenshot:

A screenshot showing the COUNTIF formula applied to count positive numbers in Excel

2. Then press Enter key, and all of the positive numbers have been counted and the result is shown in the cell. See screenshot:

A screenshot showing the result of counting positive numbers in Excel

Notes:
  • If you want to count the number of negative values only, please apply this formula =COUNTIF($A$1:$D$7,"<0").
  • And in the above formula, A1:D7 is the data range that you want to use.

Sum only positive or negative numbers in a range with formulas

In Excel, the SUMIF function can help you to add up only the positive values or negative values. You can apply this formula as this:

1. Type this formula =SUMIF($A$1:$D$7,">0") in a blank cell, see screenshot:

A screenshot showing the SUMIF formula applied to sum positive numbers in Excel

2. Then press Enter key, and just all of the positive numbers have been added up.

A screenshot showing the result of summing positive numbers in Excel

Notes:
  • To sum only negative numbers in a range, use this formula =SUMIF($A$1:$D$7,"<0").
  • In the above formula, A1:D7 is the data range that you want to use.

Using Kutools AI to count and sum only positive or negative numbers

Kutools AI saves time and effort by delivering results with a single command, eliminating the need for formulas.

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 installing Kutools for Excel, go to the "Kutools AI" tab and select "AI Aide" in the "AI" group. Then "Kutools AI Aide (Preview)" pane will appear on the right side of the worksheet. To use it, follow these steps:

  1. Select the range of numbers you wish to analyze.
  2. In the "Kutools AI Aide" pane, enter the command describing your desired operation.
    Here I instructed Kutools AI: Please count the number of positive cells in the selection and generate the result. You may need to adjust the command's keywords to suit your specific needs.
  3. Click the "Send" button or press Enter.
    A screenshot of the Kutools AI Aide pane with instructions to count positive numbers in Excel
  4. Kutools AI will process the command and provide feedback. Click the "Execute" button to see a dialog box that displays the results.
    A screenshot showing the Kutools AI Aide result for counting positive numbers in Excel
Notes:
  • Kutools AI for other specific tasks:
    • Count the number of negative values:  Please count the number of negative values in the selected range and generate the result.
    • Sum the number of positive values:  Please sum the number of positive values in the selected range and generate the result.
    • Sum the negative values:  Please sum the number of negative values in the selected range and generate the result.
  • Kutools AI can do much more than just basic calculations as above ā€” it can generate formulas, analyze data, explain formulas and so on based on your commands. Explore the full capabilities of Kutools AI and discover how it can streamline your Excel tasks. Download now to start simplifying your data management!

Whether through traditional formulas or advanced tools like Kutools AI, you can efficiently manage and analyze data involving positive or negative numbers. Practice these methods to become proficient in making the most out of Excel's robust analytical features, ensuring your data works for you in the most effective way possible. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Related articles:

How to sum / count bold numbers in a range of cells in Excel?

How to count the number of error cells / non error cells in Excel?

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