Lookup multiple columns
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 |
Lookup multiple columns
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() "TJ" wrote: 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 |
Lookup multiple columns
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 |
All times are GMT +1. The time now is 08:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com