Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006

    Unanswered: Count Instances of String in Columns

    This may be more of an excel question that it is a db question per say, but maybe someone would be kind enough to help me anyway. I have an excel spreadsheet and one column in entitled "Categories" populated by different categories (along with when these categories were used, who used them, etc.). I would like to have excel count the number of times a particular category is used, for each category, so a cell for category A, another for category B, etc. The main idea is to create a pie chart comparing the number of each category to the others. I could do this manually of course, but would find it much more productive in the future if I could learn to use excel to automate it. Your help would be appreciated. Thanks!

  2. #2
    Join Date
    Oct 2003
    Howdy. In an area to the side of your data, say in Col K (I can't see your data layout), beginning in K2 then down, enter all the various categories. You can do that using the Advanced Filter to copy unique values somewhere else.

    I assume your Categories is in Column C. Then in L2, put this formula:


    Then copy that down as far as you have categories in column K.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2006
    Another approach that may be suitable is an Excel pivot table. Select a cell within your data block and via menu path data, pivot table ... . A wizard should start and guide you through the creation process. Be aware that they do not automatically refresh (for example on changes to your source data).

    Or, if you have a database background, you might like a query table. Via menu path data, import external data, new database query. You can query the existing workbook from itself. It might be easier to first give your data block a defined name and to have saved the file to disk at some time. You can then edit the SQL to give you the DISTINCT category & COUNT required.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts