count formula
 

I need some help please,

I am using excel 2000

I have Data stored in one worksheet called Jan, in another workshet I have
summaries, and that is where I need to enter the formula.

Worksheet Jan has column F with sales people initials, and columb O with
sales numbers of accessories. In the summary sheet I need a formula that
tells me: Of all the sales a sales person had, how many had also
accessories sales.

Example:
sales person Accessories
Sue 100
Jim 100
Jim 500
Sue 300
Sue 200
Sue
Sue
Jim

The result of the formula for Sue should be 3
The result of the formula for Jim should be 2

I appreciate the help.................................

Thank you

count formula
 

Hi,

Maybe this

=SUMPRODUCT((Jan!A1:A100="Sue")*(Jan!B1:B100<""))

in practice Id have a list of salespeople on the summary sheet and do it
like this

=SUMPRODUCT((Jan!$A$1:$A$100=A1)*(Jan!$B$1:$B$100< ""))

and drag down

Wher A1 = Sue
B1=Joe
etc

Mike



"mdcgpw" wrote:

I need some help please,

I am using excel 2000

I have Data stored in one worksheet called Jan, in another workshet I have
summaries, and that is where I need to enter the formula.

Worksheet Jan has column F with sales people initials, and columb O with
sales numbers of accessories. In the summary sheet I need a formula that
tells me: Of all the sales a sales person had, how many had also
accessories sales.

Example:
sales person Accessories
Sue 100
Jim 100
Jim 500
Sue 300
Sue 200
Sue
Sue
Jim

The result of the formula for Sue should be 3
The result of the formula for Jim should be 2

I appreciate the help.................................

Thank you

count formula
 

=SUMPRODUCT((Jan!A1:A100="Sue")*(Jan!B1:B100<""))
etc for the other names
--
Gary''s Student - gsnu200826


"mdcgpw" wrote:

I need some help please,

I am using excel 2000

I have Data stored in one worksheet called Jan, in another workshet I have
summaries, and that is where I need to enter the formula.

Worksheet Jan has column F with sales people initials, and columb O with
sales numbers of accessories. In the summary sheet I need a formula that
tells me: Of all the sales a sales person had, how many had also
accessories sales.

Example:
sales person Accessories
Sue 100
Jim 100
Jim 500
Sue 300
Sue 200
Sue
Sue
Jim

The result of the formula for Sue should be 3
The result of the formula for Jim should be 2

I appreciate the help.................................

Thank you