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!

Most of us are familiar with the simple Filter tool found in the Sort & Filter group on the Data tab. If you apply the filter and use the SUM function, it will total Both visible And hidden data.

But what if you want total Only the Visible Data, and not the hidden rows that are shown in the filtered data? This is where the SUBTOTAL function becomes useful! One of the easiest ways (and little known ways) to apply the SUBTOTAL function is to:

1. First Filter your dataset
2. Select the cell directly below the range you wish to total
3. Click on the AutoSum button

Rather than using the SUM function, Excel will under these circumstances automatically use the SUBTOTAL function and will provide you with a total of Only the Visible Data. How Cool is that!

Just be sure to filter your data first, and then hit AutoSum. It couldn’t be easier!

Watch that Date!

Most of us these days have gotten into the habit of using four digits when referring to a year. It is convenient, of course, to use just two digits, but Beware, Excel uses some rules that may Surprise you when entering dates by using just two digits for the year. Two-digit year dates from 00 to 29 are interpreted as 21st century dates, but years from 30 to 99 are understood by Excel as 20th century dates.

For example, if you enter 7/4/29, Excel construes your entry as July 4, 2029. If, however, you enter 7/4/30, Excel interprets it to be July 4, 1930!

The reason this is so is because Excel uses a default boundary year of 2029. This can be changed, if you wish, by going to the Date, Time, Language, and Regional Options category on your Control Panel. Then click on Change the format of numbers, dates, and times and enter a different date on the Date tab after clicking the Customize button.

When working with data, Surprises can be Bad. The best practice is, obviously, to use four digits when entering the year. Of course, there is that Convenience thing…

Duplicates Revisited

Recently a reader wrote in saying he was combining two large mailing lists and wanted an easy way to remove the duplicates. When it comes to removing unwanted duplicates in a database, users of Excel 2007 and Excel 2010 have it easy. Here is how you accomplish this useful task:

1.  Select the entire database (be careful not to select any self-generating key fields)
2.  Go to the Data tab in the Data Tools group and click Remove Duplicates
3.  Select the columns on which you want to base your removal and click OK
4.  A message will pop up telling you how many (if any) values were found and removed

But what if you are still using Excel 2003? Well, it is not quite as slick, but there is a reasonably quick way to do this with the older software as well (by the way, this also works with the newer versions):

1.  Select the field with the duplicates
2.  Go to Data / Filter / Advanced Filter
3.  Check the box in the lower left that says Unique records only
4.  Put the starting cell of the new range in the Copy to dialogue box
5.  Click OK

Yes, it certainly is easier and more versatile removing duplicates using Excel 2007 or Excel 2010 (one more good reason to upgrade). It truly is easier to do this with the newer software (and Easy is Good!)

Whichever version of Excel you have, this will help you Maintain the Integrity of your database. So, give it a try: Get rid of those Duplicates!