Thursday, November 10, 2011

Summarize Lists

Here is an easy, but highly useful technique. Let’s say that you have a Database of All Customers and the City in which they reside. It would quite probably be interesting to all of the stakeholders of this information to see a Summary of how many Customers you have in Each City.

 Well, this is quite simple to do, but is a good review, especially since we are going to once again mention Named Ranges.

First of all, Name the Range that contains the City. This can be done simply by selecting the range, (you can include blank cells not yet filled to allow for future growth), and type the Name of the Range in the Name Box in the upper-left-corner of your worksheet. For illustration, we will assume you have named it “City” (Clever, eh?...).

Then you can list the cities in the database, and (assuming your first entry is in A2) put in the following formula in the first adjacent cell:

  =Countif(City, A2)

After that, just copy the formula down next to each city and, Bamm!, You have your Summary!

Is this Simple? Yes, it is, but often times, the Simple Ways are the Best…

No comments: