Hello Excellers and welcome back to another #FormulaFriday #Excel blog post in my Excel series. So, today I show you how to count how many cells have negative and positive numbers. In Excel, the COUNTIF function counts the number of cells in a range that have a specific value. This is useful for counting how many negative numbers or positive numbers are in a data set. In this tutorial, we will show you how to use the COUNTIF function to count cells with negative or positive numbers. We will also provide an example so you can see how it works. Let’s get started!
What Is The COUNTIF Function.
COUNTIF is an Excel Function which counts cells that meet a specific single condition. the function is used to count cells that contain text, numbers or dates. We can also use operators such a >,< and =.
So, in the scenario for today’s blog, I need to count how many positive (sales) and negative (refunds) numbers are in my data set. You can see a list of sales values in my sample data set below.
Recap of The COUNTIF Function In Excel.
The COUNTIF Function syntax is very simple.
=COUNTIF(range, criteria)
where
range is the range of cells that you want to count
criteria this refers to the criteria that determine which cells to count
Exmaple 1. COUNT NEGATIVE NUMBERS
In the first scenario, I need to know how many of my sales are negative or refunds. The criteria, therefore, I need to use is to find those cells that are less than zero (“<0”). There are a few ways to do this.
- Type the criteria right in the formula.
- Use a value in another cell as criteria. Use the & to concatenate.
You can see both formulas below.
Example 2. COUNT POSITIVE NUMBERS
The second scenario counts how many sales or positive number are in the data set. The criteria, as in the first example can be entered directly into the formula or can be stored in another cell. In this example the criteria is referred by concatenation. In this case, I need to count the cells with values greater thank (“>0”).
Both variations of the formula are below.
Things To Note With The COUNTIF Function In Excel.
Points of note, rules or guidelines when using the COUNTIF Function are listed below.
1, COUNTIF is not case-sensitive.
2. COUNTIF only supports one condition.
3. Text strings in criteria must be enclosed in double quotes (“”), i.e. “apple”, “>32”, “ja*”
4. Cell references in criteria are not enclosed in quotes, i.e. “<“&A1
5. The wildcard characters ? and * can be used in criteria.
6. COUNTIF requires a range of cells.
6. String over 255 characters cannot be matched.
7. Finally, COUNTIF will return a #VALUE error when referencing another workbook that is closed.
So, that is how to count how many cells contain positive or negative numbers. In this blog post I demonstrated how to use the Excel countif function to count positive and negative numbers. I hope this has been helpful and that you will be able to put what you’ve learned into practice. Finally, if you have any questions, please don’t hesitate to reach out for help. Thanks for reading!
Even More Excel Reading.
For more Excel and VBA tips then sign up for my Monthly Newsletter. I share 3 Tips every month. Receive my free Ebook, 50 Excel Tips and check out all of my Formula Friday Blog posts below.
How To Excel At Excel – Formula Friday Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help with Excel FAST. Why not check it out?.
