Sir,

I have tried many many time to upload the the excel file (zipped) but
in vain. so I present what the Array forumula is intended to do here :

1. if column A is "ABC" go to table at worksheet A; otherwise go to
worksheet B;
2. try to check how many item numbers in column B,D,F,H,J,L,N,P with
respective table to see if column G of the table for respective item
number is showing "Y". If it is so, count it i;.
3. Should result of the counting of step 2 above is bigger than 0, then
the result should show "Y".

All above step have to be done in a Array formula.

Please note :

i. The first column of tables in A and B worksheets are not in
ascendency order;
ii. There may be empty rows in the tables.

My Array Formula is as follow :

{=IF(SUM(IF(ISERROR(LOOKUP(N(OFFSET($B2,,{0,2,4,6, 8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G $210))),0,IF(LOOKUP(N(OFFSET($B2,,{0,2,4,6,8,10,12 ,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))= "Y",1,0)),IF(ISERROR(LOOKUP(T(OFFSET($B2,,{0,2,4,6 ,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$ G$210))),0,IF(LOOKUP(T(OFFSET($B2,,{0,2,4,6,8,10,1 2,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210)) ="Y",1,0)))=0,"","Y")}

I can send my excel file to you via email.

THANK YOU !!


T. Valko;3600755 Wrote:
Can you rephrase this:
-
What I want to achieve with this formula is to
count how many items as shown in respective
cells in column B,D,F,H,J,L,N,P are bearing
'Y" in G column in respective table in 'A'
worksheet and 'B' worksheet.-

--
Biff
Microsoft Excel MVP


"hkgoodwill" wrote in message
...-

What is the problem with the array formula (see cells in column R,
which
are highlighted coloured yellow) ?

{=IF(SUM(IF(ISERROR(LOOKUP(N(OFFSET($B2,,{0,2,4,6, 8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G $210))),0,IF(LOOKUP(N(OFFSET($B2,,{0,2,4,6,8,10,12 ,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210))= "Y",1,0)),IF(ISERROR(LOOKUP(T(OFFSET($B2,,{0,2,4,6 ,8,10,12,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$ G$210))),0,IF(LOOKUP(T(OFFSET($B2,,{0,2,4,6,8,10,1 2,14})),IF($A2="ABC",A!$A$2:$G$140,B!$A$2:$G$210)) ="Y",1,0)))-
0,"Y",""}-

What I want to achieve with this formula is to count how many items as
shown in respective cells in column B,D,F,H,J,L,N,P are bearing 'Y" in
G
column in respective table in 'A' worksheet and 'B' worksheet.

Please help !!




--
hkgoodwill -




--
hkgoodwill