Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    mountains, North Carolina, USA
    Thanked 0 Times in 0 Posts

    Counting data (Excel 97 SR 1)

    I have an Excel spreadsheet with a rather large amount of data. The data is names and addresses of company clients. What I would like to do develop an associated sheet which will allow me to count how many of the clients are in each state, or in a particular city etc. Don't want to seem too dumb; but, is there an easy way to do this without setting up a special column, putting 1 in that column and then doing a conditional sum? I know Access probably provides an easy way to do this; but, we dont have it so thats not an option.

    Thanks in advance for any help.

    Larry P

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Counting data (Excel 97 SR 1)

    You can use a pivot table to get a summary of the data
    You can use countif/sumif for single criteria
    For multiple criteria:
    If you want interaction, you can use AUTOFILTER combined with SUBTOTAL function to get info on the visible data
    You can use adv filter and criteria to filter
    you can set up criteria and then use D-functions to get stats
    You can use ARRAY formulas to get multiple criteria (see Chip Pearson for a discussion on this)

    None of these require intermediate columns

    If you give us more details, we could provide more specific info.

Posting Permissions

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