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!

Matching Two Databases

Many of us are faced with matching data that is contained in two or more databases. It may be matching customers with product choices, insurance policyholders with claims, and any great number of things. What is often the case is that there will be matches only for some of the data, as is the case in our example today of matching Policyholder Database with a Claims Database.

In our example, we will use the MATCH function, along with an error-handling function, IFERROR.

Here is what you do:

1) In the Claims database, Name your range of address in the database "ClaimsAddresses” (or whatever suits your fancy).
2) Create a "Match" range of addresses on the Policies database
3) Put the following formula in in the first cell of the new "Match" range:
=IFERROR(MATCH($M2, ClaimsAddress, FALSE), "")
4) Fill the formula to the bottom of the Match range with a double-click
5) Sort the Policy database according to the Match range

Voila! The result is a list of policies that have a claim that are indexed to the Claims database.

Try this out! Seriously, this can save you a lot of work when you have multiple databases.  All the best…

Frequency Tables and Histograms

Last October, I wrote about the FREQUENCY Function in Excel. This function allows you to investigate how frequent values within particular ranges occur. This unique tool works as an Array function that counts the number of values that occur in each specified interval (or “Bins”, as they are often referred).

The FREQUENCY Function syntax is as follows:

FREQUENCY(data_array, bins_array)

The function is entered as an Array formula after you select a range of adjacent cells (B2:B7 in our example) into which you want the distribution to appear. After you select the data and bins arrays, press CONTROL+SHIFT+ENTER.

The following example shows the “Bins” under the heading Claims, and the Frequency of claims within each range under Instances:

Now comes the fun part where we make the Histogram.

Select the table A1:B7 and create a simple Column Chart. Right-Click one of the columns in the chart and choose Format Data Series. On the Series Options tab change the Gap Width to 0% (best practice for Histograms). Finally, on the Fill tab, put a check mark in the box next Vary colors by point.

There you have it!  A finely done Frequency Table and accompanying Histogram.  Far Out!

Hiding Duplicate Values

There are times when you have duplicate data that you want to retain, but for the sake of improved readability, you wish to hide it. One way to do this is to use the built-in Hide function in Excel, but it may not always be the best choice.

Another way to hide your data is to use Conditional Formatting. You can do this to hide (Camouflage, actually) the duplicate values, and make your table a lot easier to read. The following Before and After examples illustrate this:

Before

After

In this example, when the table is sorted by State, Conditional Formatting has been used to give the duplicate occurrences of each State name a White Font color.

Here is How You Do This:

In Excel 2007 and 2010:
1. Sort your data
2. Select range B3:B12
3. On the Ribbon, go to the Home tab and click Conditional Formatting
4. Choose New Rule
5. Click Use a Formula to Determine Which Cells to Format
6. For the formula, enter: =B3=B2
7. Click the Format Button
8. Select White as the font color (same color as the cell)
9. Click OK twice

In Older Versions of Excel:
1. Sort your data
2. Select range B3:B12
3. Choose Format/Conditional Formatting
4. From the dropdown, choose Formula Is
5. For the formula, enter: =B3=B2
6. Click the Format button
7. Select White as the font color (same color as the cell)
8. Click OK twice

That’s it! Your table is much cleaner and easier to read, and you didn’t even break a sweat setting it up. How cool is that!