# Multiple IF conditions and Arrays

#### loosenut

##### New Member
Hi,

I am trying to extract the type of activity (column I) a specific drilling rig (column Z) will be performing between two dates (columns AP and AO). Essentially the arguements should be for all rows containing the rig name (z2) in column A, whose end date (column J) is <=AP and start date (column K) >=AO, then tell me the type of activity (column I), otherwise 0). Am I close, is there a better way to do this? The answer seems to always report "0" even if there is data?

{=IF(AND((\$A\$2:\$A\$1500=Z2),(\$J\$2:\$J\$1500<=AP\$1),(\$K\$2:\$K\$1500>=AO\$1))=TRUE,\$I\$2:\$I\$1500,0)}

Loosenut

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### NBVC

##### Well-known Member
Try evaluating the formula with just Enter. Your sample shows the curly parenthesis which leads me to believe you used Ctrl+Shift+Enter. That worked for me.

##### MrExcel MVP
loosenut said:
Hi,

I am trying to extract the type of activity (column I) a specific drilling rig (column Z) will be performing between two dates (columns AP and AO). Essentially the arguements should be for all rows containing the rig name (z2) in column A, whose end date (column J) is <=AP and start date (column K) >=AO, then tell me the type of activity (column I), otherwise 0). Am I close, is there a better way to do this? The answer seems to always report "0" even if there is data?

{=IF(AND((\$A\$2:\$A\$1500=Z2),(\$J\$2:\$J\$1500<=AP\$1),(\$K\$2:\$K\$1500>=AO\$1))=TRUE,\$I\$2:\$I\$1500,0)}

Loosenut

Care to post 5 rows from each range the formula you have refers to along with the expected result(s)?

#### loosenut

##### New Member
Almost Works!

Ok, So I typed in the adjustments to cell AB2, and it worked (output was "TRUE")! EXCEPT, then I tried to copy the formula down the column (cell AB3), the answered changed to "FALSE", even though it is the identical formula (I copied it by cell, and also copied it by entering the formula, selecting it, copying it and pasting the formula in a new cell). All the cells have absolute references. Is my computer possessed??? The clue is that when I copied it across the row, the answer was replicated "TRUE". It's the same forumla - shouldn't it be the same whether copied down a column or accross a row??

So Close!

Loosenut

Cell AB2: =IF(AND(('Fleet List Input UDW'!\$A\$2:\$A\$1500='Fleet List Input UDW'!\$AH\$25),('Fleet List Input UDW'!\$J\$2:\$J\$1500<='Fleet List Input UDW'!\$BW\$1),('Fleet List Input UDW'!\$K\$2:\$K\$1500>='Fleet List Input UDW'!\$BW\$1))=TRUE,TRUE,FALSE)
Output: TRUE

Cell AB3: =IF(AND(('Fleet List Input UDW'!\$A\$2:\$A\$1500='Fleet List Input UDW'!\$AH\$25),('Fleet List Input UDW'!\$J\$2:\$J\$1500<='Fleet List Input UDW'!\$BW\$1),('Fleet List Input UDW'!\$K\$2:\$K\$1500>='Fleet List Input UDW'!\$BW\$1))=TRUE,TRUE,FALSE)
Output: FALSE

Cell AC2: =IF(AND(('Fleet List Input UDW'!\$A\$2:\$A\$1500='Fleet List Input UDW'!\$AH\$25),('Fleet List Input UDW'!\$J\$2:\$J\$1500<='Fleet List Input UDW'!\$BW\$1),('Fleet List Input UDW'!\$K\$2:\$K\$1500>='Fleet List Input UDW'!\$BW\$1))=TRUE,TRUE,FALSE)
Output: TRUE

#### loosenut

##### New Member
One more Clue

Even when I cut and paste the cell that is working, it still changes the answer from "TRUE" to "FALSE". When I paste it back, it turns back to "TRUE"............ The mystery continues....

Loosenut

#### NBVC

##### Well-known Member
Seems to be working properly for me.

I used
Code:
``=IF(AND((Sheet2!\$A\$5:\$A\$1504=Sheet2!\$AH\$29),(Sheet2!\$J\$5:\$J\$1504<=Sheet2!\$BW\$4),(Sheet2!\$K\$5:\$K\$1504>=Sheet2!\$BW\$4))=TRUE,TRUE,FALSE)``
I get all Trues or all Falses, when copied down, depending on what I have in the referenced cells and ranges.

I might suggest to try opening another sheet and testing it there (or better yet another workbook). Perhaps, there is a formatting issue?

#### loosenut

##### New Member
I think it May be a limitation of the formula

So I copied it into a separate worksheet and tried it again (pasted below). Cell E2 and E3 Should be exactly the same result because it should be checking the whole range. So my conclusion is that something in the if statement only works if the entry in column D, happens to equal the entry in column A in the same row, and the rest of the conditions also match. I'd like it to work so that the entry in column D does not have to be in the same row as column A (i.e. checks the range in A). Let me know if you have any thoughts.

Thanks!

Loosenut

Cell: e2

=IF(AND((\$A\$2:\$A\$1504=\$D2),(\$B\$2:\$B\$1504<=E\$1),(\$C\$2:\$C\$1504>=E\$1))=TRUE,"TRUE","FALSE")

Cell: e3

=IF(AND((\$A\$2:\$A\$1504=\$D3),(\$B\$2:\$B\$1504<=E\$1),(\$C\$2:\$C\$1504>=E\$1))=TRUE,"TRUE","FALSE")
Book4.xls
ABCDEF
1RigNameActivityStartDateEndDate2/28/20073/31/2007
2BelfordDolphin1/18/20078/31/2007BelfordDolphinTRUETRUE
3BelfordDolphin11/29/20061/18/2007BelfordDolphinFALSEFALSE
4BelfordDolphin8/28/200411/29/2006BelfordDolphinFALSEFALSE
5DeepwaterDiscovery9/1/20089/2/2008DeepwaterDiscoveryFALSEFALSE
6DeepwaterDiscovery8/1/20067/31/2008DeepwaterDiscoveryTRUETRUE
7DeepwaterDiscovery9/10/200511/4/2005DeepwaterDiscoveryFALSEFALSE
8DeepwaterExpedition9/1/20089/2/2008DeepwaterExpeditionFALSEFALSE
9DeepwaterExpedition9/3/20069/1/2008DeepwaterExpeditionTRUETRUE
10DeepwaterExpedition8/2/20069/3/2006DeepwaterExpeditionFALSEFALSE
11DeepwaterExpedition6/23/20068/2/2006DeepwaterExpeditionFALSEFALSE
12DeepwaterExpedition6/18/20066/23/2006DeepwaterExpeditionFALSEFALSE
13DeepwaterExpedition2/3/20066/18/2006DeepwaterExpeditionFALSEFALSE
Sheet1

#### NBVC

##### Well-known Member
Does this work in E2 copied down? :

=IF(SUMPRODUCT(--(\$A\$2:\$A\$1504=\$D2),--(\$B\$2:\$B\$1504<=E\$1),--(\$C\$2:\$C\$1504>=E\$1))>0,"TRUE","FALSE")

I am not if this topic is becoming confusing now: If we still haven't gotten there, perhaps, without discussing formulas, tell us what you are trying to accomplish and what your data looks like to begin with.

#### loosenut

##### New Member
Success!

NBVC,

The formula worked like a charm! I now have a beautiful chart of tight the supply of offshore oil rigs is going to be for the next five years (tight). Thanks for sharing your expertise!

Best,

Loosenut

P.S. Any suggestion as to where to find instructions for the advanced syntax you used in your sumproduct formula? (i.e. --) Thanks...

Replies
0
Views
150
Replies
2
Views
195
Replies
3
Views
157
Replies
1
Views
327
Replies
3
Views
352

1,196,027
Messages
6,012,954
Members
441,740
Latest member
abaz21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back