Excel Sage

Real knowledge is to know the extent of one's ignorance.

The best you can do with a COUNTIFS function.

Hi All,

I thought to go for a vacation writing the last post titled ”The Magical combination of IF, AND, OR functions! for the year. But somehow this is tempting me to write this magical and very interactive formula.

Though it may look a very common, known and popular function as many people may think, it is very powerful if used properly. A small chunk of people knows its full potential. We will see that now.

This is having its own uses when combined with some other functions as a helping agent.

But I don’t want to insult this function portraying it, just as a helper!

Let’s see the family tree of the COUNT function. COUNT is the mother of all the count functions i.e. COUNT, DCOUNT, COUNTA, DCOUNTA, COUNTBLANKS, COUNTIF, and COUNTIFS.

I am not going to discuss about DCOUNT, DCOUNTA in this post as we are going to have a special discussion when we deal with the Database functions.

Let’s go!

The syntaxes for all the COUNT functions:

=COUNT(value1,value2..)

What it means? It asks us the range of cells to be selected before it counts the no of non-blank cells.

Remember it counts only the values ignoring the text and blanks.

=COUNTA(value1,value2..)

COUNTA counts everything including the text, values ignoring the blank cells.

=COUNTBLANK(range)

Well, this function doesn’t care if you have text or values in the range. What it cares about is blank cells in a range as the name says. It just counts the no of blank cells in a range.

Countifs1

Download the Excel file here and Go to worksheet named ”COUNT”

=COUNTIF(range, criteria)

COUNT-IF counts the no of items meeting the criteria in a selected range.

In plain English:  =COUNTIF(in this range, items meeting this criteria).

You can use only one condition with this function.

=COUNTIFS(range1, criteria1, range2, criteria2..)

This works same as COUNTIF but with multiple criteria as you can see in the above syntax.

It behaves like this: =COUNTIFS(in this list1, on this criteria1, in this list2, on this criteria2)

Let’s see the example below:

Countifs2

Download the Excel file here and Go to worksheet named ”COUNTIF”

In the ”Items” range i.e. E2:O2 look for ”Car” and in the ”Color” range E3:O3 look for the color ”Red” and count them all on matching both the conditions. It’s so simple, right!

The kick of this function gets stronger as the conditions go on increasing.

We will enjoy that kick as well now by taking multiple conditions and with the help of Data validation.

Now we will take the function to the next level.

This example shows a customer database of different banks with many A/c details.

Countifs3

Download the Excel file here to understand it better:  Open the worksheet named ”COUNTFS”.

Probably this data may be useful to assess the ”No of target customers” before a financial institution launches a new investment product in the financial market.(At least it will be useful in those lines).

Jumping into the example, this is very straight forward function with many conditions to be fulfilled as I said above. Download the example file to get the clear understanding of what I am talking about!

The syntax:

=COUNTIFS($C$2:$C$2501,O4,$E$2:$E$2501,N5&O5,$F$2:$F$2501,N6&O6,$G$2:$G$2501,N7&O7,$H$2:$H$2501,N8&O8,$J$2:$J$2501,N9&O9,$K$2:$K$2501,N10&O10)

In the above syntax, all the arguments in the Black are ”Criteria Ranges and all the arguments in the Red are ”Criteria.

With the help of Data validation you can actually interact with this COUNTIFS function in this example.

By changing the <, >, <>, = operators in the column N, you can feel the power of a COUNTIFS function.

How is the kick!

If you like this article, please comment and get registered to receive my newsletter every week!!

We are going to have a new Excel forum in the next couple of days, so that everybody can post their queries and get answers instantly.

That’s the news from me.

Keep coming for many more tasty articles.

Ravi

Excel Sage

, , , , ,

4 thoughts on “The best you can do with a COUNTIFS function.
  • asifalishaik says:

    Hello Ravi,

    I am a regular reader of your blog.Your posts are so informative and I am applying in my office environment wherever those are applicable. Please write something about Excel’s OFFSET function in your next post.

    Asif Ali Shaik
    Lahore

  • DaveK says:

    Hi Ravi,

    Here’s are two interesting questions for you:

    1. I am importing similar information to your last example, except in one column, there maybe a blank space before or after an entry (ie so “Chicago” and ” Chicago” could both be entered. How can I account for this ?

    2. What if a column contains two pieces of info together – say a prefix and a value (ie: Houton963) ? What i would like to do is use the Left and Right functions in conjucntion with the Countifs function to get the desired answer I want. Oh, and I want to do this in one cell, like you have done above.

    Any suggestions would be helpful.

    Thanks

    DAve

    • Excel Sage says:

      Hello Dave, You can use text to columns feature in Excel which is the fastest method to parse text. But if you want to parse the alphanumeric cell contents by using a formula, Use this formula- =LEFT(A10,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A10&”0123456789″))-1). I assumed your alphanumeric content is in the A1 cell. For counting with parsing using COUNTIF, use this formula – =COUNTIF(H1:H8,TRIM(LEFT(A10,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A10&”0123456789″))-1)))…where in H1:H8 are having the repetitions of City Names. I hope I have cleared your queries, I welcome more if any.

Leave a Reply

5 visitors online now
1 guests, 4 bots, 0 members
Max visitors today: 6 at 12:53 am IST
This month: 12 at 07-11-2014 04:52 pm IST
This year: 15 at 03-06-2014 09:56 am IST
All time: 40 at 05-04-2013 09:17 pm IST