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:
- A function
- 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.
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.
- 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.
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.
Step 3: Click Insert and Select Module
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.
Step 3: Click Insert and Select Module
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
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
This closes the File Option Dialog box
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”.
