Thursday, May 26, 2011

COUNTIF and CONCATENATION Revisited

Dear Readers,

It has been a rough week for me. My one true hero since childhood, my Uncle Bob, (after whom I was named), passed away. He was a fine man in so many ways.

I haven’t written much on the COUNTIF function for the last two years, so I thought it was time to revisit this truly powerful weapon all Excel Enthusiasts should have in her or his arsenal. I has a potentially tricky syntax (I have learned this the hard way) that I want to Emphasize in this week’s blog.

COUNTIF is a truly powerful tool that is frequently useful. It can be used by itself, or combined with other functions to make it even more effective. One simple way to maximize the use of this function is to reference a cell location through the use of CONCATENATION (simply an “&” in this case).

For instance, if you wish to count only cells that are greater than the value in a particular cell, (B2 in the example below), you should use Quotation Marks for the Greater Than symbol, and the & preceding the cell reference. Any time you insert criteria (other than a cell reference) in a COUNTIF formula, you must be sure to use Quotation Marks or is simply won’t work.

=COUNTIF(A1:A29, ">"&B2)

How cool is that?! COUNTIF combined with CONCATENATION makes a dynamite combination for creating interactive reports. Give it a try!

No comments: