Hi, Biff.

Thanks for your assistance - Regarding the "redundancy" - the second IF was
the ELSE of the first - If the column B is not null, then add all occurrences
of column B instead of applying a lookup.

I have taken your advice, and "played" almost all day - herewith what I have
now - but this is still not giving me what I expect - the numbers make no
sense at all, as I am expecting 48 as a total, and only get 8 (7 and 1 for
each of the SumProduct functions below).

Logic Explanation
When column B (Queue) of the Defects file is null, I need to use its column
C (Queue Owner) to lookup a value in the QueueOwnerList file, and then only
those Queues from that file that are equal to the value in my current sheet's
B12. If column B of the Defects file is not null, simply add to the count
those queues that are equal to B12 (PS. this second SumProduct works 100%).

=SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"),
--('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=""),
--(ISNUMBER(MATCH('[Defects
20070604.xls]Sheet1'!$C$1:$C$2000,'[QueueOwnerList.xls]lookup
names'!$A$1:$A$2000,0))),
--('[QueueOwnerList.xls]lookup names'!$B$1:$B$2000=$B12))+
SUMPRODUCT(--('[Defects 20070604.xls]Sheet1'!$A$1:$A$2000="OMS"),
--('[Defects 20070604.xls]Sheet1'!$B$1:$B$2000=$B12))

Am I misunderstanding something or doing something totally stupid?

Kind regards,
Pete.

"T. Valko" wrote:

You have conflicting conditions:

IF($B$1:$B$2000=""
IF($B9=$B$1:$B$2000

Unless B9 is empty, in which case those expressions would be redundant,
those expressions can't be TRUE at the same time.

The VLOOKUP/INDEX can be expressed in a SUMPRODUCT function as:

--(ISNUMBER(MATCH(C1:C2000,'[Lookupfile.xls]Name'!$A$1:$A$2000,0))),
--('[Lookupfile.xls]Name'!$B$1:$B$2000=$B9)

But, you have to straighten out those IF(B1....... things!

Biff

"Crazy Pete" <Crazy wrote in message
...
Within the attached array formula, I need to either perform a lookup, or
use
the data I have per iteration, to count for various values in column B.
However, as I understand, VLOOKUP requires a single value or reference as
the
lookup_value. I have tried using the INDEX function (see below), but it
appears to only pick up row 1 for each iteration, hence not matching with
any
valid lookup values.

How do I, inside an array calculation, specify the row which has been
found
to match the preceding IF statements?

Effectively, I wish to perform a lookup on another workbook, using column
C
and the row which has been found to match the two preceding IF statements.

=SUM(IF($A$1:$A$2000="OMS",IF($B$1:$B$2000="",IF(V LOOKUP(INDEX($A$1:$C$2000,ROW($1:$2000),3),'[Lookupfile.xls]Name'!$A$1:$B$2000,2,FALSE)=$B9,1,0),IF($B9=$B$1:$ B$2000,1,0)),0))

regards,
Pete.