How to Use the COUNTIF Function in Excel

Excel can do extra than simply basic math. That is due to its bevy of built-in capabilities and min-formulas that simplify the creation of extra complicated formulation.

In my decade-long expertise with Excel, I’ve discovered that one of many extra helpful capabilities is the COUNTIF perform.

Download 10 Excel Templates for Marketers [Free Kit]

You should utilize COUNTIF to depend the variety of cells that include a particular worth or vary of values. It’s simpler to make use of COUNTIF than to manually depend your self.

Tips on how to Use the COUNTIF Perform in Excel

The COUNTIF perform in Excel counts the variety of cells in a variety that meet the given standards. It doesn’t whole the cells; it merely counts them. I’ve discovered it helpful for counting cells that include a particular worth or vary of values.

For instance, let’s say you’ve a spreadsheet that comprises buyer contact data, together with avenue addresses and ZIP codes. You possibly can simply use the COUNTIF perform to depend what number of clients dwell in a given ZIP code — and also you don’t even must type the addresses by ZIP code to do it.

Let’s work by the method step-by-step.

1. =COUNTIF()

Start by coming into the next into the cell the place you wish to place the reply:

=COUNTIF()

For this instance, we’ll use a grocery checklist that I’ve written. The totally different gadgets I wish to purchase are sorted by sort, like greens and fruit.

2. Outline a variety of cells.

For the COUNTIF perform to work, you must enter two arguments between the parentheses — the vary of cells you’re taking a look at and the standards you wish to match.

Place your cursor inside the parentheses and both manually enter the vary of cells (e.g., D1:D20) or use your mouse to focus on the vary of cells in your spreadsheet.

Assuming your ZIP code values are in column D from row 1 to row 20, the perform ought to now appear like this:

=COUNTIF(A2:A35)

3. Add a comma.

Subsequent, sort a comma after the vary, like this:

=COUNTIF(A2:A35,)

4. Outline your search standards.

You now must enter the factors or worth that you just wish to depend after the comma, surrounded by citation marks.

In our instance, let’s say you’re trying to see what number of greens are in your checklist. On this occasion, the factors you’re counting is Vegetable, and your perform ought to now appear like this:

=COUNTIF(A2:A35, Vegetable)

Observe that your standards generally is a quantity (“10”), textual content (“Los Angeles”), or one other cell (C3). Nonetheless, in the event you reference one other cell, you don’t encompass it with citation marks. Standards are usually not case-sensitive, so you could possibly enter “Crimson,” “pink,” or “RED” and get the identical outcomes.

5. Activate the perform.

Press Enter, and the perform prompts, returning the variety of cells that match your argument.

Ideas for Utilizing the COUNTIF Perform

Many customers, myself included, have found that you need to use the COUNTIF perform in many various methods in addition to counting particular values. Listed below are three ideas I like to recommend for extending using the COUNTIF perform.

Use wildcard characters for partial matches.

You don’t must reference a particular worth or standards. For those who solely know a part of the worth you wish to depend, you need to use the * wildcard character to match any worth in that a part of the worth.

For instance, let’s say you’ve an inventory of addresses. If you wish to match all ZIP codes that begin with the numbers 46 (corresponding to 46032, 46033, and 46450), you’d enter 46 adopted by the * wildcard, like this:

=COUNTIF(D1:D20,46*)

You should utilize the wildcard character at both the start or the tip of the worth string. For instance, to depend all cells that finish with the letters “polis,” enter the next:

=COUNTIF(D1:D20,*polis)

It will depend cells that include the cities of Indianapolis and Minneapolis.

Rely values which are better than or lower than a quantity.

For those who’re working with numbers, chances are you’ll wish to depend cells with values better than or lower than a given worth. You do that through the use of the mathematical better than (>) and fewer than (<) indicators.

To depend all cells which have a price better than a given quantity, corresponding to 10, enter this:

=COUNTIF(D1:D20,>10)

To depend cells which are better than or equal to a quantity, enter this:

=COUNTIF(D!:D20>=10)

To depend all cells which have a price lower than a given quantity, enter this:

=COUNTIF(D1:D20<10)

To depend cells which have a price lower than or equal to a given quantity, enter this:

=COUNTIF(D1:D20<=10)

You possibly can even depend cells with a price not equal to a particular quantity. For instance, to depend cells that aren’t equal to the quantity 10, enter this:

=COUNTIF(D1:D20<>10)

In all these situations, do not forget that the factors, together with the lower than, better than, and equal indicators, have to be enclosed inside citation marks.

Rely one worth OR one other.

The COUNTIF perform may also be used to depend a number of standards—that’s, cells that include one worth or one other.

For instance, you would possibly wish to depend clients who dwell in both Los Angeles or San Diego. You do that through the use of two COUNTIF capabilities with a + between them, like this:

=COUNTIF(D1:D20,Los Angeles)+COUNTIF(D1:D20,San Diego)

So as to add much more values, enter one other + and COUNTIF perform.

If you wish to get much more out of Excel, take a look at our article on the right way to use Excel like a professional. You’ll discover 29 highly effective ideas, methods, and shortcuts that may make Excel even simpler to make use of.

Getting Began

For those who’re trying to depend the variety of gadgets that match particular standards, the COUNTIF perform is the best way to go. You may simply type on that column and manually depend the entries, however utilizing COUNTIF is a complete lot simpler.

Now, attempt it out and save your self a while.

excel marketing templates

Latest news
Related news

LEAVE A REPLY

Please enter your comment!
Please enter your name here