Excel’s mysterious Criteria parameter in the COUNTIF function

A revised version of this content is now available at blog.officewizkid.com


Here is a classic situation:

I have an Excel file with data column, the data column holds multiple strings. Let’s say something like in the table below:

Excel’s mysterious Criteria parameter at the COUNTIF function

If my goal was to count the number of rows that has the exact value of “Money” then the function was simple:

=COUNTIF(A:A,”Money”)

And the value in the above example would have been 1.

But what if my goal is to count the number of rows that contain the value “Money” regardless if this is the complete value or part of a bigger string?

Apparently there is a simple way to do that:

=COUNTIF(A:A,”*Money*”)

The value for the previous data set is 5, as it counts the cells containing any of the following strings:

something money something, Money, somthingmoneysomething, somethingmoney, moneysomething

In fact the COUNTIF function is a little bit more sophisticated than it initially looks. The criteria part can have a wildcards (such as *) and also numeric comparison functions such as “>” or “<”.

You can find more information on the criteria part of COUNTIF at the following place:

http://office.microsoft.com/en-us/excel-help/countif-HP005209029.aspx

Advertisements

Author: dave

Consider myself kid in soul and naive by choice. I am interested in people, technology and business and thrive when they all work together. My favorite quote and motto is that “You can fool some people some times but you cant fool all the people all the time” ― Bob Marley

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s