Solved

Array formula to bring back results if column DI20 onwards has a value of 1 in a cell

Posted on 2016-10-04
14
33 Views
Last Modified: 2016-10-04
Hi Experts using Excel 2010

I have the following formula which works just fine….

Array Formula
=INDEX('Data Entry'!BF$20:BF$532,SMALL(IF('Data Entry'!$BF$20:$BF$532>=$C$1,IF('Data Entry'!$BF$20:$BF$532<=$F$1,ROW('Data Entry'!$BF$20:$BF$532)-ROW('Data Entry'!$BF$20)+1)),ROWS(A$6:A6)))

However, i want to add an additional step into the above formula so the formula also looks in column DI20 onwards and if the cell has a value of 1 return back the result.
0
Comment
Question by:route217
  • 6
  • 5
  • 3
14 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

pls try

=INDEX('Data Entry'!BF$20:BF$532,SMALL(IF(AND('Data Entry'!$DI$20:$DI$532=1,'Data Entry'!$BF$20:$BF$532>=$C$1),IF('Data Entry'!$BF$20:$BF$532<=$F$1,ROW('Data Entry'!$BF$20:$BF$532)-ROW('Data Entry'!$BF$20)+1)),ROWS(A$6:A6)))

Open in new window

Regards
0
 

Author Comment

by:route217
Comment Utility
Hi Rgonzo

The formula returns #num! When I drag it down the first cell returns the correct value then errors
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Could you send a sample
0
 

Author Comment

by:route217
Comment Utility
Tricky...to...do so..
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Entered as array?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Maybe
=INDEX('Data Entry'!BF$20:BF$532,SMALL(IF('Data Entry'!$DI$20:$DI$532=1,IF('Data Entry'!$BF$20:$BF$532>=$C$1),IF('Data Entry'!$BF$20:$BF$532<=$F$1,ROW('Data Entry'!$BF$20:$BF$532)-ROW('Data Entry'!$BF$20)+1))),ROWS(A$6:A6)))

Open in new window

0
 

Author Comment

by:route217
Comment Utility
Yes..as an array...and apologise that I cannot upload a file
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
Formula posted by Rgonzo HERE should work for you.
0
 

Author Comment

by:route217
Comment Utility
Ok rgonzo I have attached a file...with test data...not sure what u may have done wrong.
test-data_.xlsx
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You need to wrap the formula within IFERROR like this....
=IFERROR(INDEX('Data Entry'!A$2:A$6,SMALL(IF('Data Entry'!$DI$2:$DI$6=1,IF('Data Entry'!$BF$2:$BF$6>=$C$1,IF('Data Entry'!$BF$2:$BF$6<=$F$1,ROW('Data Entry'!$BF$2:$BF$6)-ROW('Data Entry'!$BF$2)+1))),ROWS(A$3:A3))),"")

Open in new window


The column DI on Data Entry Sheet doesn't meet the criteria i.e it is blank and it doesn't contain 1 in any of its cell.
0
 

Author Comment

by:route217
Comment Utility
Not sure what I may have done worng..typo
0
 
LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 250 total points
Comment Utility
Please refer to the attached.
test-data_.xlsx
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 250 total points
Comment Utility
Corrected file
test-data_V2.xlsx
0
 

Author Comment

by:route217
Comment Utility
Many thanks and much appreciated experts for the excellent feedback.

Posting one more question in relation to above..
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now