Excel Sage

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.

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:

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.

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