Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
Count formula | Excel Discussion (Misc queries) | |||
Trying to construct a count count formula | Excel Discussion (Misc queries) | |||
Need a formula to count and add | Excel Discussion (Misc queries) | |||
Count formula Help... | Excel Discussion (Misc queries) |