Problem with a Array formula, Please help !!

[hkgoodwill]

What is the problem with the array formula (see cells in column R, whic
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,12,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 a
shown in respective cells in column B,D,F,H,J,L,N,P are bearing 'Y" in
column in respective table in 'A' worksheet and 'B' worksheet.

Please help !

 

[hkgoodwill]

What is the problem with the array formula (see cells in column R, whic
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,12,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 a
shown in respective cells in column B,D,F,H,J,L,N,P are bearing 'Y" in
column in respective table in 'A' worksheet and 'B' worksheet.

Please help !!

Sorry I fail to upload my zip file. I dont know why (note : size <15kb

 

[T. Valko]

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.

 

[hkgoodwill]

Please wait, please me think how to present my question clearly late
!!

 

[hkgoodwill]

Sir,

I have tried many many time to upload the the excel file (zipped) bu
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 t
worksheet B;
2. try to check how many item numbers in column B,D,F,H,J,L,N,P wit
respective table to see if column G of the table for respective ite
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, the
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 i
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,12,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]

Try posting a small sample of your data.

I don't need to see every column, B,D,F,H,J,L,N,P. Maybe just a few.
Something like this:

....B...D...F...G
....1...1...2...Y
....0...5...4.....
....2...1...3...Y
..............2...Y

 

[T. Valko]

P.S.

Also, tell me what result you expect.