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

1. ## 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.

2. ## 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.

How to use PivotTables
Excel 2000 Tutorial: PivotTable Reports 101

3. ## 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. ## 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
•