Range: it is simply the range in which you want to count values.
Condition: This is where we tell Excel what to count. It can be a specific Text (should be in “”), a number, a logical operator(=,><,>=,<=,<>) and wild card operators (*,?).
Let’s understand COUNTIF Statement with some easy examples
Note: COUNTIF is not case sensitive. A and a, both are treated equally.
To count a specific text in range in excel, always use double quotes (“”). I have this data in a spread sheet.
In range A1:D13 i have my data. Now one query has arrived. How many times did Thanos win? In column B I have names of winners.
So in cell G2 I write this excel COUNTIF formula:
You don’t need to use double quotes (“”) to count numeric values. Let’s see an example first.
Now, after the first query, another query appears. How many times have there been 0 prices?
To answer this, I wrote this COUNTIF formula in cell G3.
After answering the last query, I got another query, i.e. How many times was the price more than 50 Rs?
So now we need to count all values that are greater than 50.
While using numeric operators (=,>,<,>=,<=,<>) you need to use double quotes.
So now in cell G3, I wrote this COUNTIF formula:
It returns 5. Notice the double quote. Now, if your condition value was in some other cell, say in E4 than you would write:
We have another query here. How many times have there been gold winners? Now it is mentioned in D. But it contains the names of the winners too.
If you write COUNTIF(D2:D13,”Gold”). It will return 0. Because no cell contains Gold according to excel. But it does. To tell Excel to count any value that ends with Gold, we would write this COUNTIF formula in cell G5:
I will return the correct value which is 3
Now the COUNTIF if function counts every value that ends with gold. The * operator just accepts anything written before Gold. If you write *Gold*, then COUNTIF will count anything that contains Gold in it.
The ? operator is used when you know how many letters there are, but you don’t know exactly what they are. For example, if I want to count all the Delhi pin numbers from a range, then I know they start at 1100 and are in total 6 digits. So my search criteria will be “1100??”.
Try this one by yourself and let me know what you found. Use the comments section below to ask questions and give suggestions. They are highly appreciated.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.