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:
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…