We often have negative numbers in our * Excel* dataset. Negative numbers can create a problem while calculating in

*. So, it is important to count the negative numbers present in a dataset. In this article, we will discuss how to count negative numbers in*

**Excel***.*

**Excel**In this blog, we will discuss 3 effective methods to count negative numbers in Excel. In the first method, we will use **the COUNTIF function** to count the negative numbers. Next, we will opt for **the SUMPRODUCT function** to do the task. Finally, we will use a **VBA **code to do our calculation. We will use the following sample dataset to carry out our method illustrations.

## 1. Using COUNTIF Function to Count Negative Numbers in Excel

In this method, we will use **the COUNTIF function** to count the negative numbers. Follow the subsequent steps to carry out the task.

**Step 1:**

- Firstly, choose the cell in which to display the outcome of the count of the negative numbers.
- In this instance, the cell is
.**G5** - Then, write the following formula in cell
.**G5**

`=COUNTIF(E5:E10,"<0")`

- Here,
is the range of cells where you want to search for the negative numbers.*E5:E10* - “
**<0**” or “**less than zero**” is the condition on which the function will return the result. - This function returns an array of
and*True*.*False* - To convert those
and*True*into numbers*False*and*0*, we will use the two negative signs at the beginning of the function.*1* - Finally, hit
.*Enter*

**Step 2:**

- As a result, the cell will display the quantity of negative numbers in the chosen cell range.
- In our case, the result is
.*2*

**Read More: ****How to Add Negative Numbers in Excel**

## 2. Applying SUMPRODUCT Function to Count Negative Numbers

In this example, we will opt for ** the SUMPRODUCT function** to count the negative numbers. Follow the ensuing steps to do it.

**Step 1:**

- Firstly, decide which cell will contain the results of the count of the negative numbers.
- In this instance, we will select the
cell.**G5** - After that, enter the following formula into cell
.**G5**

`=SUMPRODUCT(--(E5:E10<0))`

- In this case, the range of cells where you want to look for the negative numbers is
.*E5:E10* - The condition on which the function will return the result is “
” or “*0*“.*less than zero* - Lastly, press
.*Enter*

**Step 2:**

- Consequently, the cell will return the number of negative numbers in the selected range of cells.
- In our case, the result is
**2.**

**Read More: ****Excel Formula to Return Zero If Negative Value is Found**

## 3. Use of Excel VBA Code to Count Negative Numbers

In this method, we will resort to a **VBA **code to count the numbers of the negative numbers in our dataset. Adhere to the outlined steps below to complete the task.

**Step 1:**

- Firstly, select the range of cells where you want to look for the negative numbers.
- In our case, the range is
.*E5:E10*

**Step 2:**

- Secondly, go to the
tab.*Developer* - From the
tab, select the*Developer*command.*Visual Basic* - Consequently, a
dialogue box will appear.*Visual Basic*

**Step 3:**

- Thirdly, in the
tab, click on*Visual Basic*.*Insert* - Then, select the
option.*Module* - Consequently, a coding
will appear.**Module**

**Step 4:**

- In the coding module, write down the following code.
- Then,
the code.*save*

```
Sub Counting_negative_numbers()
'declaring variable'
Dim cell_rng As Range
'setting value for the variable'
Set cell_rng = Application.Selection
'applying the COUNTIF function'
Cells(5,6).Value = Application.WorksheetFunction.CountIf(cell_rng, "<0")
End Sub
```

**VBA Code Breakdown**

Here, we will declare the variable as Range type variable.**Dim cell_rng As Range:**This implies that the cell_range variable will be assigned the value of the selected range of cells.**Set cell_rng = Application.Selection:**This means,**Cells(5,6).Value = Application.WorksheetFunction.CountIf(cell_rng, “<0”):****the COUNTIF function**will go through the values of thevariable which is in this case the values in the selected range of cells. The function will impose its condition which is all values that are less than zero in all the cells and return the number of values which satisfies the condition.**cell_rng**

**Step 5:**

- Finally, go to the
tab and click on it.*Run* - From the drop-down option, select the
command to run the code.*Run*

**Step 6:**

- As a result, you will find that Excel will return the number of negative numbers from the selected range.
- In this instance, the result is
**2**.

**Read More: ****How to Show Negative Numbers in Excel**

**Download the Practice Workbook**

## Conclusion

Negative numbers in a dataset could be a tricky business. So, Excel users need to** keep track of the negative numbers.** The users can easily do that by counting the negative numbers. After going through this article, the readers will have three effective methods to count the negative numbers in their dataset. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. Happy Learning!

## Related Articles

**How to Put a Negative Number in Excel Formula****How to Add Brackets to Negative Numbers in Excel****How to Change Positive Numbers to Negative in Excel****How to Apply Formula for Positive and Negative Numbers in Excel****[Fixed!] Excel Not Adding Negative Numbers Correctly****Excel Negative Numbers in Brackets and Red****How to Make a Group of Cells Negative in Excel****How to Make Negative Numbers Red in Excel****How to Convert Negative Value to Positive in Excel Using Formula****Excel Formula If Cell Contains Negative Number****Excel Formula to Return Blank If Cell Value Is Negative****How to Sum Negative and Positive Numbers in Excel**