I have a spreadsheet that has an rep log on in column H and a date in column F. This is on Sheet 2

I currently have a count of the number of items a rep has worked on Sheet 1 but I also need a count of items the rep closed. Closed is reflected by the Date in column F. IF Column F is populated with a at I want to get a count on that by rep.

I would believe this is possible somehow but I have hit a wall trying to get there.

Any assistance would be greatly appreciated.

Hi,

A non-formula approach would be to use a pivot table. This is probably the best choice because it will give you a lightning quick overview of the data.

In terms of formula approaches:
If you are using XL2007 or later you can use COUNTIFS().
If you are using XL2003 or earlier you can use SUMPRODUCT() or DCOUNTA().

I hate to admit it but I am using Office 2000, here at work. As for using the Sumproduct I have seen that but keep getting an error when I am trying to get the formula for the date column.

As for the pivot table I have never done one... Guess this is as good a time as any.. will have to research how.

Hi,
If you're using XL 2000, one thing to be careful of is not to use entire column references within SUMPRODUCT because the function will return an error. This rule is true up to and including XL 2003.
Other typical causes of an error return value are error values within the data or not keeping the arrays an equal size (depending on the form of SUMPRODUCT() you are using).

Something like this should be fine:
=SUMPRODUCT(--(Sheet2!F2:F200="a"),--(Sheet2!H2:H200="Rep A"))


Good choice. I don't have XL 2000 to hand to check, but if memory serves me correctly Pivot Tables were introduced in XL 2000, so you are in luck.

OK Colin any suggestions on setting up the pivot table. Each time I attempt to set one up my computer locks up.

The spreadsheet has 2100 lines on it and growing and 21 columns. I only need information from 2 maybe 3 columns and only on the rows that have information in those columns.

I have used the wizard and I lock up with virtual memory low.

This is all new to me any assistance or direction would be greatly appreciated.

Hi,

Have a read though this article which describes the various pivot table wizard options in XL 2000 and explains how the choices affect memory usage:
How to optimize PivotTable performance in Excel 2000

A data set of that size really shouldn't be an issue. It's hard for me to give specific directions because I only have Excel 2007 at home which is quite different!