Thursday, November 7, 2013

Summarizing Your Data

Summarizing your data is essential for providing and maintaining Information for your business. Let’s say that you have a database of your Sales by State. The Database may contain thousands of records, and it would quite probably be interesting to all of the stakeholders of this data to see a Summary of the Number of your Sales by each State.

This is, of course, not difficult to do, but a review of a couple of Key Techniques is always a good thing. The Keys in this instance are:

1.  Naming Your Ranges
2.  Using the COUNTIF Function

First of all, Name the Range that contains the State. You can do this by selecting the range in your database, (including blank cells below for future growth), and typing 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” (Oh, these clever Americans…).

Then in your Report Table (as in our example above), you can list the States that are included in the database. Assuming your first entry is in A2, put in the following formula in the first adjacent cell:

 =COUNTIF(State, A2) 

Then just copy the formula down next to complete your report table and, Presto! You have a Summary of the Number of Sales by State! 

Using these Simple Techniques can quickly give you the Information you are looking for. Give it a try!

No comments: