count formula

[mdcgpw]

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

 

[Mike H]

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

 

[Gary''s Student]

=SUMPRODUCT((Jan!A1:A100="Sue")*(Jan!B1:B100<>""))
etc for the other names