Displaying summary lists from master tasklist

[mr_hiboy]

Hi,


I am looking to pull back a reference number (using large) from a list where the department column = "Marketing" and Status column = "Active"


I am using the following array.

{=LARGE(IF(AND(Tracker!$C$11:$C$1500="Active",Tracker!$P$11:$P$1500="Marketing"),Tracker!$H$11:$H$1500),1)}


For clarity Column C is the status, P is Department H is the reference number.


I was hoping this would pull back the largest reference number where both these criteria was met.


I have tested both parts and it works fine, but when I add the AND it returns zero.


Any ideas?


Thanks

Paul

 

[Luke M]

Your thinking is correct, the only problem is that the AND function forces a single value output. Since you're dealing with an array, that's causing a problem. A similar way to force an "and" type logic is to multiply the arrays together.


{=LARGE(IF((Tracker!$C$11:$C$1500="Active")*(Tracker!$P$11:$P$1500="Marketing"),Tracker!$H$11:$H$1500),1)}


The advantage is that the multiplication retains all the values (returns 1's and 0's) and uses that to determine which values from the True array should be fed into the LARGE function.

 

[mr_hiboy]

Ah ha! Brilliant Luke, appreciate your help