How To Count Negative Numbers In Excel

Are you trying to count negative numbers in Excel but not sure where to start? Then, you’ve come to the right place!

I know what it’s like when you get a request from your boss to calculate something but you have no idea what to do! 

So, in this article, I’ll show you how to count negative numbers in Excel using different methods. Following these easy steps, you’ll be able to calculate your totals easily and quickly.

How To Count Negative Numbers

There are several ways to count negative numbers in Excel. Here are examples for each function:

  • My default go-to formula is using the COUNTIF function – =COUNTIF(range, “<0”)
    • This will count the number of cells in the specified range that contain a negative number. If you want to include zeros in the count, then use “<=0” in the criteria parameter
COUNTIF Example
  • Using the SUMPRODUCT function – =SUMPRODUCT((range<0)*1)
    • Same as the COUNTIF, this formula counts the number of cells in the specified range that contain a negative number.
  • Using the SUM function – =SUM(IF(range<0,1,0))
    • This will use the IF function to determine which cells in the range are negative, and then sum up the number of cells that are negative.
  • Using the COUNT function – =COUNT(range)-COUNTIF(range, “>=0”)
    • This will count the total number of cells in the range and then subtract the number of cells that contain a non-negative number, giving you the number of negative numbers.
Count Negative Formulas

Told you it was easy!

How To Calculate Sum Of Negative Numbers In Excel

Here is an example of how to calculate the sum of negative numbers in Excel:

  • Use the SUMIF function – =SUMIF(range,”<0″,range)
    • The formula checks the first “range” for the negative values and then sums the range. Both “range” parameters need to be the same.
  • Using our example, you should get -339
SUMIF example

If you want to sum only the positive numbers, you can use the same formula but replace the “<0” with “>0”

How To Only Show Negative Numbers In Excel

Depending on the need, I choose between a filter or conditional formatting. Here’s how you can apply both methods.

Using An Excel Filter

  • Highlight the range that you want to filter.
  • Go to the “Home” tab and click on “Filter” under the “Sort & Filter” menu.
  • Click on the filter dropdown arrow for the column that you want to filter.
  • Under the filters, select “Less Than“. Use “Greater Than” if you want to show positive figures only.
  • Enter “0” in the value field and click “OK”. This will filter out all positive numbers and only show negative numbers.
Filter negative values

Apply Conditional Formatting

  • Highlight the range that you want to apply formatting to.
  • Go to the “Home” tab and click on “Conditional Formatting“
  • Click on “New Rule“.
  • In the “New Formatting Rule” window, select “Only format cells that contain” from the dropdown.
  • In the next dropdown, select “Less than” and enter “0” in the value field.
  • Click on the “Format” button and select the formatting options you want to apply to negative numbers.
  • Click “OK” to apply the formatting.

Thanks for reading and I hope this helps! Let me know if you have any questions.

Other Excel tutorials you may also like:

Leave a Comment