Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
lookup across multiple columns | Excel Worksheet Functions | |||
Lookup tables with multiple columns | Excel Discussion (Misc queries) | |||
LOOKUP w/ multiple search columns? | Excel Discussion (Misc queries) | |||
Lookup + Sum multiple columns | Excel Worksheet Functions |