Counting is the act of tallying a certain quantity to obtain or to reveal the number of items or several objects in a set. Counting can be general and done in almost all aspects of life or our daily routines.
In most cases, we do the tallying or the counting in numerical values that involve the use of numbers. The majority of activities or scenarios we happen to come across in our daily lives will always involve counting in one way or the other. Nearly everything depends on or revolves around counting in this world.
Counting values greater than or less than is just obtaining the numeric value of values larger than or less than 0. Greater than is another word used in the place of larger than or huge than, or higher than to mean big.
We adopt some of the following steps to guide us in the counting of cells greater than 0 in any given data set or scenario in excel sheets.
Steps to count cells greater than 0
1. To do the counting of the cells first you need to have the dataset to use in place or ready. To do this, open an excel sheet and create some dummy data into it as in the case below.
2. The next step is to get the count of all the cells which happen to be greater than 0. We do this with the help of a formula; COUNTIF. In our data set, if we need to find the count of cells greater than 0 as from cell A1 all the way to cell C1, we type the formula in the formula bar in this format =COUNTIF (A1: C1,">0")
Upon clicking on the enter button, the formula will count for you and return the cell values that are greater than 0 in the selected cells. For our case, the cells that have values greater than 0 is 2, this is to mean the two values are greater than 0 or are positive numbers, unlike the ones that are negative which will be less than zero.
How to count Cells greater than or equal to zero
1. Assuming we have the following set of data
2. Select an empty cell,
3. Enter the following formula on the formula bar and press enter, =COUNTIF(A1: C5,">=0")
How to count cells with values less than zero
1. Let's use the following data as our example to count cells with values less than zero
As you can see we have some negative values in it. Select an empty cell
2. Enter the following formula on the formula bar and press enter =COUNTIF(A1: C4,"<0")
As you can see we have 5 cells less than 0
COUNTIFS To Count Greater Than Zero and Less Than 10
You use the formula below;
Range- ranges of cells to be selected.
Criteria- number of cells that need to be assigned.
COUNTIF function always counts the number of cells within the range that qualify to fit given conditions.
Assume you have been provided with player statistics across 15 matches. Then task is to determine how many matches end with this player scoring. You will use the formula below to determine the number of matches with goals.
You can also, use the formula to also count cells that are greater than zero, but less than 2. in this example, we shall have the number of matches that the player only contributed to 1 goal.
Use the formula syntax below to count cells that are greater than 0 and less than 10.
For your example;
=COUNTIF(C5:C19,">0") – COUNTIF(C5:C19,">=2")
NOTE: The COUNTIFS function enables you to use multiple ranges and specify multiple criteria.
Counting Cells Greater Than 0(Zero) By Combining COUNTIF & COUNTIFS Under Multiple ‘OR’ Criteria from Different Columns
1. In Cell F13, the formula to use will be as follows;
=COUNTIF(C5:C19,">0") + COUNTIF(D5:D19,">0") – COUNTIFS(C5:C19,">0",D5:D19,">0")
2. Finally, tap the Enter button.
How Does The Formula Work?
In your formula, you can see Plus (+) is being used in between two COUNTIF functions, which means you are finding your results separately.
Assume that you want to find the number of matches a player has scored goals and facilitated assists. Your return value will be;
9+8=17 (Goals+ assists).
Now, the COUNTIFS function will determine matches in which the player has scored both goals and provided assists. You will find the value to be 6.
When you minus the result from the value in (.) one the final answer will be 10
Using COUNTIF Along with Ampersand (&) To Count Cells Greater Than 0(Zero)
From your example above you can determine the matches a player scores and enhance assists by use of Ampersand (&).
In your example use Column D.
1. You use the formula below in Cell F13;
2. Now, tap the Enter button. You will notice the player had made 8 assists in 15 matches played.
3. You use Ampersand (&) after Double-Quotes to link the "Greater Than” criteria with 0.
Counting Cells Greater Than 0(Zero) With COUNTIFS Under Multiple ‘AND’ Criteria from Different Columns
If you are subjected to adding many criteria while counting cells greater than 0, you have to use the COUNTIFS function.
Here again, you can use the example of a player with the goals scores scored as well as assists provided.
1. In your Cell F13 type the formula
2. Tap the Enter button. You will notice that the player has both assists and goals that are 6 times out of the matches played 15.
3. To add multiple criteria basically you need to need use Comma (,) to distinguish the two criteria.