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.
