Thursday, February 25, 2016

Summarizing Your Data

Oftentimes, the simplest solution is the best approach. Let’s say that you have a large Database of Company Clients and the State in which they reside. Since it would likely be interesting to all of the stakeholders in the company to see a Summary of how many Clients you have in Each State, you have decided to use a bit of Excel magic to produce this summation.

If you have read this blog for any length of time, you know that I am a great believer in Named Ranges, so first of all, Name the Range that contains the Clients. 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. In this example, we will assume you have named it “State”.

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

  =Countif(State, A2)

Then it is a simple matter of copying the formula down next to each city and, Presto!, You have your Summary of Clients!

As I like to say, oftentimes the Simple Ways are the Best…

No comments: