Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Count -- Use Arrays? (2000, SR1)

    I want to create a formula that will count my records based on two conditions:

    For example,
    Column E has a "salesperson" in it.
    Column F has a "response date in it."

    I want to know how many responses (rows) meet two criteria: 1) the name of a particular salesperson and 2) falls in a particular month. On my Results sheet, I have the salespersons listed in column A, and the months listed in row 1. Obviously, I will need to do a bunch of these -- one for each salesperson, and one for each month.

    I've tried using the Conditional Sum Wizard add-in, but it seems to work only sporadically, and then, the formula cannot be copied. So I have to recreate it a million times, and the end user of this sheet will likely be unable to accomplish that! I'd like her to be able to just copy and paste or fill down.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Count -- Use Arrays? (2000, SR1)

    A pivot table will do exactly what you want, without formulas.
    Click somewhere in your data, then select Data | PivotTable and PivotChart Report.

    For more info about Pivot Tables, see the Excel help, or the following links:
    How to use PivotTables
    Excel 2000 Tutorial: PivotTable Reports 101

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Count -- Use Arrays? (2000, SR1)

    Thanks, that's a good idea, and something I hadn't really thought about.

    The problem is, I need the results (the final counts) to be "stuck" in the spreadsheet, because other I have other Excel spreadsheets that must reference them. Can a pivot table do that for me, and show all the results for all the salespersons/months at the same time?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Count -- Use Arrays? (2000, SR1)

    A pivot table will display the results for all months and all salespersons. If necessary, you can then copy the result, and use Edit | Paste Special... > Values to create a static version.

Posting Permissions

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