The right way to Use the COUNTIF Operate in Excel

Excel can do extra than simply simple arithmetic. That is because of its bevy of built-in features and min-formulas that simplify the creation of extra advanced formulation.

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

You need to use COUNTIF to rely the variety of cells that comprise a selected worth or vary of values. It’s simpler to make use of COUNTIF than to manually rely your self.

The right way to Use the COUNTIF Operate 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 comprise a selected worth or vary of values.

For instance, let’s say you have got a spreadsheet that accommodates buyer contact info, together with road addresses and ZIP codes. You possibly can simply use the COUNTIF perform to rely what number of clients dwell in a given ZIP code — and also you don’t even should kind the addresses by ZIP code to do it.

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

1. =COUNTIF()

Start by getting into the next into the cell the place you need to place the reply:

=COUNTIF()

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

2. Outline a variety of cells.

For the COUNTIF perform to work, it’s a must to enter two arguments between the parentheses — the vary of cells you’re and the standards you need 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 spotlight 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, kind a comma after the vary, like this:

=COUNTIF(A2:A35,)

4. Outline your search standards.

You now have to enter the factors or worth that you just need to rely after the comma, surrounded by citation marks.

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

=COUNTIF(A2:A35, “Vegetable“)

Word that your standards could be a quantity (“10”), textual content (“Los Angeles”), or one other cell (C3). Nevertheless, for those who reference one other cell, you don’t encompass it with citation marks. Standards will not be case-sensitive, so you could possibly enter “Purple,” “crimson,” 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.

Suggestions for Utilizing the COUNTIF Operate

Many customers, myself included, have found that you should utilize the COUNTIF perform in many alternative methods moreover counting particular values. Listed here are three suggestions I like to recommend for extending the usage of the COUNTIF perform.

Use wildcard characters for partial matches.

You don’t should reference a selected worth or standards. When you solely know a part of the worth you need to rely, you should utilize the * wildcard character to match any worth in that a part of the worth.

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

=COUNTIF(D1:D20,“46*“)

You need to use the wildcard character at both the start or the top of the worth string. For instance, to rely all cells that finish with the letters “polis,” enter the next:

=COUNTIF(D1:D20,“*polis“)

This may rely cells that comprise the cities of Indianapolis and Minneapolis.

Depend values which are larger than or lower than a quantity.

When you’re working with numbers, you might need to rely cells with values larger than or lower than a given worth. You do that by utilizing the mathematical larger than (>) and fewer than (<) indicators.

To rely all cells which have a worth larger than a given quantity, comparable to 10, enter this:

=COUNTIF(D1:D20,“>10“)

To rely cells which are larger than or equal to a quantity, enter this:

=COUNTIF(D!:D20“>=10“)

To rely all cells which have a worth lower than a given quantity, enter this:

=COUNTIF(D1:D20“<10“)

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

=COUNTIF(D1:D20“<=10“)

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

=COUNTIF(D1:D20“<>10“)

In all these situations, keep in mind that the factors, together with the lower than, larger than, and equal indicators, should be enclosed inside citation marks.

Depend one worth OR one other.

The COUNTIF perform will also be used to rely a number of standards—that’s, cells that comprise one worth or one other.

For instance, you may need to rely clients who dwell in both Los Angeles or San Diego. You do that by utilizing two COUNTIF features 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 suggestions, tips, and shortcuts that may make Excel even simpler to make use of.

Getting Began

When you’re seeking to rely the variety of gadgets that match particular standards, the COUNTIF perform is the best way to go. You could possibly simply kind on that column and manually rely the entries, however utilizing COUNTIF is an entire lot simpler.

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