How to Count Negative Numbers in Excel

[banglabhai420]

If you have a spreadsheet with a lot of numbers, and you want to count the negative numbers, one way is to just count them sequentially. However, the Excel way is to write a formula that'll count it for you. Read on to learn the Excel way.

The COUNTIF Function

The COUNTIF function in Excel counts cells in a given range only if they meet a condition. This function takes a range of cells, tests if they fit the criteria, and then returns the number of cells that do.

The COUNTIF function's criteria supports logical operators as well as wildcards. The logical operators include:

< Less than

> Greater than

= Equal to

<> Not equal to

=< Less than or equal to

>= Greater than or equal to

You can use logical operators to set conditions that include numbers. To count the negative numbers in a range of cells, we're going to use the < logical operator. You can also use other cells as the criteria in the COUNTIF function.

=COUNTIF(range of cells, criteria)

Counting Negative Numbers

By now, you can probably gather what you know of the COUNTIF function and conclude that to count negative numbers, you need only a COUNTIF function with criteria of "<0". This way, the formula will return a number that tells you how many negative numbers there are in the cell range you indicated.

That being said, let's see the COUNTIF function at work with an example. In this example, we have the average temperatures of a cold week in Chicago.

The average temperatures are laid out in cells B2 to B8, and we want the count of days with a subzero average in cell E2.To count the days with subzero average temperatures:

Select cell E2.

Click to the formula bar and enter the formula below:

=COUNTIF(B2:B8, "<0")

This formula will utilize the COUNTIF function to test the cells from B2 to B8 and see if they meet the criteria.

Press Enter on your keyboard. The formula will return the number of days with subzero average temperatures.

There you have it! COUNTIF counting the cells with regard to the IF.

More Formulas, Less Labor

Excel is designed to make our lives easier. With the COUNTIF formula, you can forget about counting the cells yourself and let Excel do the hard work for you. The COUNTIF function lets you accomplish marvelous things with Excel, especially if you couple it with the SUMPRODUCT function.

[Silent Watcher]

Avoid unnecessary posts such as 'Thank you', 'Welcome', etc. Such posts will be deleted and user will be warned if it happens again. If caught spamming, the following actions are applicable -

• First time - Warning
• Second time - 5000 Points will be deducted
• Third time - Ban for 7 days
• Fourth time - Permanent Ban

If the post helped you, reward the user by reacting to the post like this -