# The best you can do with a COUNTIFS function.

By | December 28, 2012

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.

=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:

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!

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

## 6 thoughts on “The best you can do with a COUNTIFS function.”

1. Sid

Hi Ravi,

I have one question regarding use of LEFT function inside the criteria ‘range’ of COUNTIF function.

For example I have the following range list:
A12
B23
A65
B23
C74
C32

I want to count the entries which start with ‘A’ (in the above list the result would be 2).
For that I can use LEFT function to make another row with only first character of the above list, and use COUNTIF.
But I want to do it without creating another row, something like =COUNTIF(LEFT(\$A\$1,1):LEFT(\$A\$6,1)
I have tried above formula but it seems to be incorrect. Is there any way to do this?

Many thanks,
Sid

1. Sid

Gotcha!!
Just use asterisk after the desired Characters: compare with “A*” to get anything starting from A.

Sid

2. DaveK

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.

Thanks

DAve

1. Excel Sage Post author

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.

3. asifalishaik

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

1. excelsage Post author

Sure, Asif!
You can check my next post on OFFSET function within a few days. Thank you.
Ravi