The right way to Use the COUNTIF Serve as in Excel

Excel can do extra than simply basic math. That is due to its bevy of integrated purposes and min-formulas that simplify the introduction of extra advanced formulation.

In my decade-long revel in with Excel, I’ve discovered that probably the most extra helpful purposes is the COUNTIF serve as.

You’ll use COUNTIF to depend the collection of cells that comprise a selected worth or vary of values. It’s more uncomplicated to make use of COUNTIF than to manually depend your self.

The right way to Use the COUNTIF Serve as in Excel

The COUNTIF serve as in Excel counts the collection of cells in a variety that meet the given standards. It doesn’t overall the cells; it merely counts them. I’ve discovered it helpful for counting cells that comprise a selected worth or vary of values.

As an example, let’s say you’ve got a spreadsheet that incorporates buyer touch knowledge, together with side road addresses and ZIP codes. You’ll simply use the COUNTIF serve as to depend what number of shoppers are living in a given ZIP code — and also you don’t even must type the addresses by means of ZIP code to do it.

Let’s paintings in the course of the procedure step by step.

1. =COUNTIF()

Start by means of getting into the next into the cellular the place you need to put the solution:

=COUNTIF()

For this case, we’ll use a grocery checklist that I’ve written. The other pieces I wish to purchase are taken care of by means of kind, like fruits and vegetables.

2. Outline a variety of cells.

For the COUNTIF serve as to paintings, you must input two arguments between the parentheses — the vary of cells you’re taking a look at and the standards you need to compare.

Position your cursor throughout the parentheses and both manually input the variability of cells (e.g., D1:D20) or use your mouse to spotlight the variability of cells on your spreadsheet.

Assuming your ZIP code values are in column D from row 1 to row 20, the serve as must now appear to be this:

=COUNTIF(A2:A35)

3. Upload a comma.

Subsequent, kind a comma after the variability, like this:

=COUNTIF(A2:A35,)

4. Outline your seek standards.

You currently wish to input the factors or worth that you need to depend after the comma, surrounded by means of citation marks.

In our instance, let’s say you’re taking a look to look what number of greens are to your checklist. On this example, the factors you’re counting is Vegetable, and your serve as must now appear to be this:

=COUNTIF(A2:A35, “Vegetable“)

Observe that your standards is usually a quantity (“10”), textual content (“Los Angeles”), or some other cellular (C3). On the other hand, in the event you reference some other cellular, you don’t encompass it with citation marks. Standards aren’t case-sensitive, so it’s good to input “Pink,” “crimson,” or “RED” and get the similar effects.

5. Turn on the serve as.

Press Input, and the serve as turns on, returning the collection of cells that fit your argument.

Pointers for The use of the COUNTIF Serve as

Many customers, myself integrated, have came upon that you’ll be able to use the COUNTIF serve as in many various techniques but even so counting particular values. Listed here are 3 pointers I like to recommend for extending using the COUNTIF serve as.

Use wildcard characters for partial suits.

You don’t must reference a selected worth or standards. Should you handiest know a part of the worth you need to depend, you’ll be able to use the * wildcard personality to compare any worth in that a part of the worth.

As an example, let’s say you’ve got a listing of addresses. If you wish to fit all ZIP codes that get started with the numbers 46 (comparable to 46032, 46033, and 46450), you could input 46 adopted by means of the * wildcard, like this:

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

You’ll use the wildcard personality at both the start or the tip of the worth string. As an example, to depend all cells that finish with the letters “polis,” input the next:

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

This may depend cells that comprise the towns of Indianapolis and Minneapolis.

Rely values which can be more than or lower than a bunch.

Should you’re running with numbers, you might wish to depend cells with values more than or lower than a given worth. You do that by means of the use of the mathematical more than (>) and no more than (<) indicators.

To depend all cells that experience a worth more than a given quantity, comparable to 10, input this:

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

To depend cells which can be more than or equivalent to a bunch, input this:

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

To depend all cells that experience a worth lower than a given quantity, input this:

=COUNTIF(D1:D20“<10“)

To depend cells that experience a worth lower than or equivalent to a given quantity, input this:

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

You’ll even depend cells with a worth now not equivalent to a selected quantity. As an example, to depend cells that aren’t equivalent to the quantity 10, input this:

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

In these kind of cases, keep in mind that the factors, together with the lower than, more than, and equivalent indicators, will have to be enclosed inside citation marks.

Rely one worth OR some other.

The COUNTIF serve as may also be used to depend a couple of standards—this is, cells that comprise one worth or some other.

As an example, chances are you’ll wish to depend shoppers who are living in both Los Angeles or San Diego. You do that by means of the use of two COUNTIF purposes with a + between them, like this:

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

So as to add much more values, input some other + and COUNTIF serve as.

If you wish to get much more out of Excel, take a look at our article on how one can use Excel like a professional. You’ll to find 29 robust pointers, tips, and shortcuts that may make Excel even more uncomplicated to make use of.

Getting Began

Should you’re taking a look to depend the collection of pieces that fit particular standards, the COUNTIF serve as is learn how to pass. That you must simply type on that column and manually depend the entries, however the use of COUNTIF is a complete lot more uncomplicated.

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

WordPress SEO

[ continue ]