Lookup multiple columns

[TJ]

Hi

I have a worksheet that conatins about 1000 rows. It looks something like:-

RTM, Manager, Status

Amy, Danny, Closed
Amy, John, Open
Amy, Danny, Open
Amy, Karen, Close
Steve, Danny, Open
Steve, Danny, Open
Steve, Danny, Closed
Steve, Karen, Open.

I want to be able to run a macro/worksheet function that will output
RTM, Manager, Qty Closed

Amy, Danny,1
Amy John, 0
Amy, Karen, 1
Steve, Danny, 1
Steve, Karen, 0

I was thinking of using LOOKUP for the RTM but as far as I am aware this
will only count the number of closed for Amy but not differentiate between
Danny and Karen. Has anyone any ideas?
Thanks
TJ

 

[Guest]

try
=sumproduct(--(rtm_range="Amy"),--(manager_range="Danny"))
the "--(" changes the logical true false to a numeric 1 0
Also you cannot reference an entire column in sumproduct()

 

[merjet]

Copy the 2 columns of names somewhere below them. On the copied names
use the menu Data | Advanced Filter, selecting "Unique records only."
Then use bj's formula with cell addresses in place of the hard-coded
names, and a 3rd argument for "Closed".

Hth,
Merjet