Problem with a Array formula, Please help !!


H

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 !
 
Ad

Advertisements

H

hkgoodwill

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

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.
 
H

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

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top