Hello Excellers and welcome back to another #FormulaFriday #Excel blog post in my 2020 Excel series. Today we will look at how to count how many cells have negative and positive numbers. To do this we can use the COUNTIF Function in Excel.
COUNTIF is an Excel Function which counts cells that meet a specific single condition. It 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.
The COUNTIF Function syntax if very simple.
range is the range of cells that you want to count
criteria this refers to the criteria that determine which cells to count
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.
COUNT POSITIVE NUMBERS
For our second scenario, we will count 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 and referred to by concatenation. In this case, I need to count the cells with values greater thank (“>0”).
Below are both variations of the formula.
Things To Note With The COUNTIF Function.
There are a few points of note, rules or guidelines when using the COUNTIF Function.
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. Stong over 255 characters cannot be matched.
7. COUNTIF will return a #VALUE error when referencing another workbook that is closed.
So, that is how t count how many cells contain positive or negative numbers.
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips and check out all of my Formula Friday Blog posts below.
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?.