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

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

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

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

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

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

--
Biff
Microsoft Excel MVP


"hkgoodwill" wrote in message
...

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

P.S.

Also, tell me what result you expect.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

--
Biff
Microsoft Excel MVP


"hkgoodwill" wrote in message
...

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

hkgoodwill;3600498 Wrote:
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 !!

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




--
hkgoodwill