Clicky

Counting Negative Numbers in Excel

Counting_Negative_numbers

Have you been faced with the task of counting the number of values on a worksheet based on a specified criterion on Excel, specifically Counting Negative Numbers in Excel?

Today we will see how to Count the number of Negative Values (or Numbers) in an Array of Data consisting of both positive and negative values on Excel using:

  1. A function
  2. Visual Basic Application Code

For the purpose of this tutorial, we will be using the table shown below to understand how to carry out this task.

This table shows the average temperature of seven cities in the month of April and May and our task will be to count the number of Negative temperatures. 

counting negative numbers excel 1

Before we proceed, let us define some terminologies which you will come across in the course of this tutorial.

  • Negative Numbers: Numbers with a ‘-‘ (minus) sign in front of the Value. Eg. -5, -9.2.
  • Range: A collection of selected cells with an upper reference (the first selected cell on the left) and a lower reference (the last selected cell on the right). In our example the range is C3:D9. From the image below, you would discover that our range cuts through two columns (Column C and Column D) and seven row (Row 3,4,5,6 and 7). You will notice that Cell C3 is the Upper reference and Cell D9 is the lower reference in the range.
    counting negative numbers in excel 2
  • Criteria: This defines the condition that tells the function which cell to count. For example, if the criteria is set at less than 0, the function counts all values less than 0. The Criteria can be a number, text string, or a reference cell
  • Function: This is a pre-established formulas that performs calculations by using specific values, called arguments, in a particular order E.g., AVERAGE, COUNT, COUNTIF
  • Condition: This is an expression that evaluates if a statement is TRUE or FALSE
  • VBA – This is an acronym for Visual Basic Application. It is an event driven programming language
  • Sub: This is a short for Subroutine. A subroutine is primarily a piece of code that executes a specific task described in the code
  • Dim: This is an acronym for Declare in Memory. It is used to declare a variable to a specific data type
  • Set – This is a statement which is used to assign a value to an object

A: USING A FUNCTION (=COUNTIF (range,criteria)) for Counting Negative Numbers in Excel

Step 1: Navigate to a blank cell where you wish to have your result displayed

Step 2: Click on the Cell (In this tutorial, we have selected Cell C10 as our result cell) 

Step 3: Type in an equal to symbol (‘=’)

Step 4: Type in the COUNTIF function

Step 5: Insert an Opening bracket (‘(‘) 

Step 6: Select the range. (In our example the range is C3:D9)

Step 7: A highlighted reference to Cell C3 to Cell D9 will appear

Step 8: Insert a Comma sign (‘,’)

Step 9: Input the condition. In our example the condition is “<0”.

Note: When inputting a condition that has a text within, always use a Double Quotation mark. Excel uses the text embedded and discards the quotes while running.

Step 10: Close the bracket

Step 11: Press the ENTER key.

You should see your result appear in the designated cell.

counting negative numbers using excel 3

You can practice other conditions (such as “>0”, 1, etc) using the same dataset. 

B: USING A VISUAL BASIC APPLICATION CODE

Counting 

VBA allows you to easily automate various activities in Excel.

This is done by writing a program from the backend.

I know this sounds like a programmer’s task but trust me when I say it’s very easy.

Firstly, we need to be sure that the “Developer” Tab on your MS Excel ribbon is activated. If this isn’t the case, simply

  • Go to File
  • Click on Options from the excel dialog box
  • Click on the Customize Ribbon in the left pane
  • Check the Developers option on the right pane
  • Click OK.

Now let’s get started with VBA!

Here I will show you have to write a program for a static range and a dynamic range

USING VBA FOR A STATIC RANGE

This method is only used when the dataset range is static.

 If a change in the range is effected, the new range parameters must be updated in the program.

Let’s see how this is done!

Step 1: Click on the Developer tab on the excel ribbon

Step 2: Select the Visual Basic Icon on the Left

The VBA Window opens.

USING A VISUAL BASIC APPLICATION CODE

Step 3: Click Insert and Select Module

VBA window

A blank Module window is created.

Step 4: Copy and paste the code below

Public Function Number_of_negatives() As Integer
Dim ws As Worksheet
Dim myrange As Range
Dim totalcells As Integer
Dim Cell As Range
Set myrange = Range("C3:D9")
totalcells = 0
For Each Cell In myrange
    If Cell.Value < 0 Then
    totalcells = totalcells + 1
    End If
Next
Number_of_Negative = totalcells
End Function

Let me quickly explain what we are trying to achieve with this code

This is a For Loop program. During the code execution an analysis of all cells within the range is carried out.

For every time a cell is checked against the “<0” condition, if the response is TRUE, it documents it and moves to the next cell. 

Step 5:   Close your VBA window

Step 6: Navigate to the Cell you wish to display your answer and type in =( equal to), followed by the Subroutine name (in our example this is Number_of_negatives), opening and closing brackets.

You will discover that the subroutine name displays as a function on Excel.

Step 7: Click OK

Step 8: The answer is displayed

Using VBA FOR A DYNAMIC RANGE

Step 1: Click on the Developer tab on your MS Excel ribbon

Step 2: Select the Visual Basic Icon on the Left

The VBA Window opens.

VBA

Step 3: Click Insert and Select Module

VBA FOR A DYNAMIC RANGE

A blank Module window is created.

Step 4: Copy and paste the code below

Sub Count_negative_temperature()
Dim ws As Worksheet
Dim myrange As Range
Dim Cell As Range
Set ws = Application.ActiveSheet
Set myrange = Application.Selection
Set Cell = Application.InputBox(Title:="Designated Cell Location", _
Prompt:="What Cell do you Want your Answers?", _
Type:=8)
Cell.Value = Application.WorksheetFunction.CountIf(myrange, "<0")
End Sub

Let me quickly explain what we are trying to achieve. 

This subroutine will analyze all cells within a selection on any worksheet and count the number of negatives within.

Step 5: Close the VBA window and return to the worksheet

Step 6: Highlight the Range of cells you wish to analyze. In our example the range is “C3:D9” 

Step 7: Click on the Developer tab on the excel ribbon

Step 8: Select the Macros Icon on the Left

Step 9: A Micros dialog box pops out

Step 10: In the “Macros name” field, Select the Subroutine name you just created on your VBA (In our example, the subroutine is ‘Count_negative_temperature’

Step 11: In the “Macros In” field, select where (what worksheet) you want the program to be applied. In our example, we want the program to run in the excel document called Counting_Negative_numbers

Step 12: Click on the Run button

Counting_Negative_numbers

Step 13: A prompt pops out requesting you to select the Cell where you wish to display your answer

Step 14: Navigate to the Cell you wish to display your answer and select it. Alternatively, if you already know the Cell label, simply follow this syntax 🡪 ($Columnname$Rownumber for example $C$13)

Note: If you want to display your result in more than one cells, simple hold down your Shift key, while you select your desired cells.

Step 15: Click OK

Step 16: You answer is displayed on the designated Cell(s)

Just in case you wish to add you Macro to your quick access bar, you can easily follow the following outlined steps.

Step 1: Click on the File tab and then click on Options.

An excel ‘Option’ dialog box is opened. 

Step 2: Select Quick Access Toolbar from the list on the left pane

Step 3: Click on the ‘Choose Command From’ field

Step 4: Select Macros

Step 5: Select the desired Macros. In our example this is Counting_negative_numbers

Step 6: Click Add

You will notice that the Macro name has been pushed to the right pane

Step 7: Click OK 

Quick Access Toolbar

This closes the File Option Dialog box

file option dialog

Step 8: Your macro quick access appears on the top leftmost top of your MS Excel window

Conclusion

From this tutorial, we’ve gone over Counting Negative Numbers in Excel using several methods.

You can also experiment counting the number of Positive values in a dataset.

However, for every point where the “<0” condition was stated in the course of our tutorial, simply change it to “>0”.

Denise Elizabeth
Denise is a Financial Controller with experience in Office Software (Excel & Word), Accounting, Bookkeeping, Business Incorporation/LLC, Taxes and IRS Issues and more. She Graduated from Portland State University in Business Management and went onto Seattle University to complete her Graduate (Masters) Degree in Organizational Systems Renewal. She contributes to Computing.net with her knowledge of Business Software, in particularly Microsoft Excel and Google Sheets.