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**## Download the Practice Workbook

## 3 Handy Approaches to Count Negative Numbers in 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*

### 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.**

### 3. Applying VBA Code to Count Negative Numbers in Excel

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**.

## 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. To learn more about Excel, you can visit our website, **ExcelDemy**. Happy Learning!