Ignoring criteria or blank cells in array formula

[Fatboy10]

[TABLE="width: 668"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Exp Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Status[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]North[/TD]
[TD]East[/TD]
[TD]South[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]URGENT[/TD]
[TD]READY[/TD]
[TD]URGENT[/TD]
[TD]REQUIRED[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]1/11/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]20/05/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]1/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]15/10/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]West[/TD]
[TD]10/06/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a table (Above) that tells me when a location is going out of date. I would also like the status to report "no stock" if the matching location is not listed and "Ready" if there are no dates for that location.

The array formula used so far is {=IF(MIN(IF((Table1[Location]=H4)*(Table1[Exp Date]<>""),Table1[Exp Date]))<today()+15,"urgent",if(min(if((table1[location]=h4)*(table1[exp date]<="">""),Table1[Exp Date]))<today()+90,"required","ready"))}

For this example H4 is "North" Underlined

</today()+90,"required","ready"))}
</today()+15,"urgent",if(min(if((table1[location]=h4)*(table1[exp>

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Location[/td][td]Exp Date[/td][td][/td][td]Status[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]North[/td][td][/td][td][/td][td]North[/td][td]East[/td][td]South[/td][td]West[/td][td]Southeast[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]North[/td][td][/td][td][/td][td]REQUIRED[/td][td]Ready[/td][td]Ready[/td][td]REQUIRED[/td][td]No stock[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]East[/td][td]

1/11/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]South[/td][td]20/05/2018[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]North[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]South[/td][td]

1/6/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]East[/td][td]15/10/2018[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]West[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet40[/td][/tr][/table]

Formula in D3:
=IF(COUNTIF($A$2:$A$9,D2)=0,"No stock",IF(COUNTIFS($A$2:$A$9,D2,$B$2:$B$9,"<>")>0,"Ready",IF(COUNTIFS($A$2:$A$9,D2,$B$2:$B$9,"")>0,"REQUIRED")))

 

[Fatboy10]

Thank you Oscar,

When I put the formula in and have no dates, if there is a location with no date the status should be "Ready". I have also noticed that when I posted my initial formula it isn't all there.

My initial formula:

<today()+15,"urgent",if(min(if((table1[location]=f2)*(table1[exp date]<=""><today()+90,"required","ready"))}
<today()+15,"urgent",if(min(if((table1[location]=e2)*(table1[exp date]<=""><today()+90,"required","ready"))
{=IF(MIN(IF((Table1[Location]=D2)*(Table1[Exp Date]<>""),Table1[Exp Date])),TODAY()+15,<today()+15,"urgent",if(min(if((table1[location]=e2)*(table1[exp date]<="">"URGENT",
IF(MIN(IF((Table1[Location]=D2)*(Table1[Exp Date]<>""),Table1[Exp Date]))<today()+90,"required,"ready"))<today()+90,"required","ready"))<today()+90,"required","ready"))
<today()+90,"required","ready"))}


I have tried to adapt yours to:
<today()+15),"urgent",if(countifs(table1[location],d2,table1[exp date],table1[exp="" date]<today()+90),"required","ready"))))}
<today()+15),"urgent",if(countifs(table1[location],d2,table1[exp date],table1[exp="" date]<today()+90),"required","ready")))
{=IF(COUNTIF($A$2:$A$9,D2)=0,"No stock",IF(COUNTIFS(Table1[Location],D2,Table1[Exp Date],Table1[Exp Date]<today()+15),"urgent",if(countifs(table1[location],d2,table1[exp date],table1[exp="" date]<today()+90),"required","ready")))
<TODAY()+15),"URGENT",
IF(COUNTIFS(Table1[Location],D2,Table1[Exp Date],Table1[Exp Date]<TODAY()+90),"REQUIRED","READY")))}

Thank you again for your help</today()+15),"urgent",if(countifs(table1[location],d2,table1[exp></today()+15),"urgent",if(countifs(table1[location],d2,table1[exp></today()+15),"urgent",if(countifs(table1[location],d2,table1[exp></today()+90,"required","ready"))}
</today()+90,"required,"ready"))<today()+90,"required","ready"))<today()+90,"required","ready"))
</today()+15,"urgent",if(min(if((table1[location]=e2)*(table1[exp></today()+90,"required","ready"))
</today()+15,"urgent",if(min(if((table1[location]=e2)*(table1[exp></today()+90,"required","ready"))}
</today()+15,"urgent",if(min(if((table1[location]=f2)*(table1[exp>

 

[Fatboy10]

Sorry it is not allowing me to put in my full formula!!

Ultimately the intention is that

-if location not listed then "No stock"
-if Exp date is empty or beyond 90 days from TODAY then "Ready"
-if Exp date is between +15 but <90 from TODAY then "Required"
-if Exp date is <15 from TODAY the "Urgent"


Thank you again

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Location[/td][td]Exp Date[/td][td][/td][td]Status[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]North[/td][td][/td][td][/td][td]North[/td][td]East[/td][td]South[/td][td]West[/td][td]Southeast[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]North[/td][td][/td][td][/td][td]No stock[/td][td]Urgent[/td][td]Ready[/td][td]Required[/td][td]No stock[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]East[/td][td]

1/11/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]South[/td][td]

5/20/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]North[/td][td][/td][td][/td][td]Today[/td][td]

1/1/2018​

[/td][td]Urgent[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]North[/td][td][/td][td][/td][td]Today+15[/td][td]

1/16/2018​

[/td][td]Required[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]East[/td][td]

10/15/2018​

[/td][td][/td][td]Today+90[/td][td]

4/1/2018​

[/td][td]Ready[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]West[/td][td]

1/21/2018​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet40[/td][/tr][/table]

Formula in cell D3:
=IFNA(INDEX($F$6:$F$8,MATCH(INDEX($B$2:$B$9,MATCH(D2,$A$2:$A$9,0)),$E$6:$E$8,1)),"No stock")

Copy cell D3 and paste to E3:H3.

Formula in cell E6:
=TODAY()

Formula in cell E7:
=E6+15

Formula in cell E8:
=E6+90

 

[Fatboy10]

Thank you again Oscar but this is still not quite right.

If the Location is not listed in Column A then its status would be "No Stock"

If the Location is listed in Column A but it there are no dates at all for that location then the status would be "Ready".

If the earliest date listed against the location is within 15 days the the status would be "Urgent"

If the earliest date listed against the location is between 15 and 90 days then the status would be "Required"

and, If the earliest date listed against the location is +90 days then the status would be "Ready"[table="width: 500, class: grid"]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location[/TD]
[TD]Exp Date[/TD]
[TD][/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD]North[/TD]
[TD]South[/TD]
[TD]East[/TD]
[TD]West[/TD]
[TD]SouthWest[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]East[/TD]
[TD]01/11/18[/TD]
[TD][/TD]
[TD]Ready[/TD]
[TD]No Stock[/TD]
[TD]Ready[/TD]
[TD]Urgent[/TD]
[TD]Required[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]East[/TD]
[TD]24/12/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]West[/TD]
[TD]12/05/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]SouthWest[/TD]
[TD]27/05/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]West[/TD]
[TD]30/06/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]SouthWest[/TD]
[TD]01/06/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]SouthWest[/TD]
[TD]02/07/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fatboy10]

If the Location is not listed in Column A then its status would be "No Stock"


If the Location is listed in Column A but it there are no dates at all for that location then the status would be "Ready".


If the earliest date listed against the location is within 15 days the the status would be "Urgent"


If the earliest date listed against the location is between 15 and 90 days then the status would be "Required"


and, If the earliest date listed against the location is +90 days then the status would be "Ready"



A B C D E F G H I
1 Item Location Exp Date Status
2 Beans North North South East West SouthWest
3 Apples East 01/11/18 Ready No Stock Ready Urgent Required
4 Cream North
5 Eggs North
6 Oranges East 24/12/18 TODAY 10/05/18
7 Pears West 12/05/18
8 Apples SouthWest 27/05/18
9 Apples West 30/06/18
10 Oranges SouthWest 01/06/18
11 Bananas SouthWest 02/07/18
12 Beans North


I have the following formula which works except if an item in a list from doesn't have a Exp Date and other items from that location do, the status always shows "READY" the formula wont realise to ***** the other dates in that location.the same location


=IF(COUNTIF(Table1[Location],E2)=0,"No Inventory",IF(COUNTIFS(Table1[Location],E2,Table1[Exp Date],""),"READY",IF(MIN(IF((Table1[Location]=E2)*(Table1[Exp Date]<>""),Table1[Exp Date]))<TODAY()+15,"URGENT",IF(MIN(IF((Table1[Location]=E2)*(Table1[Exp Date]<>""),Table1[Exp Date]))<TODAY()+90,"REQUIRED",IF(COUNTIFS(Table1[Location],D2,Table1[Exp Date],""),"READY","READY")))))

 

[Fatboy10]

If the Location is not listed in Column A then its status would be "No Stock"

If the Location is listed in Column A but it there are no dates at all for that location then the status would be "Ready".

If the earliest date listed against the location is within 15 days the the status would be "Urgent"

If the earliest date listed against the location is between 15 and 90 days then the status would be "Required"

and, If the earliest date listed against the location is +90 days then the status would be "Ready"

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Location[/TD]
[TD]Exp Date[/TD]
[TD][/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beans[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD]North[/TD]
[TD]South[/TD]
[TD]East[/TD]
[TD]West[/TD]
[TD]Southwest[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Apples[/TD]
[TD]East[/TD]
[TD]1/11/18[/TD]
[TD][/TD]
[TD]READY[/TD]
[TD]No Stock[/TD]
[TD]Ready[/TD]
[TD]Urgent[/TD]
[TD]Required[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Cream[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Eggs[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]East[/TD]
[TD]24/12/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Pears[/TD]
[TD]West[/TD]
[TD]12/05/18[/TD]
[TD][/TD]
[TD][/TD]
[TD]Today=[/TD]
[TD]09/05/18[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Apples[/TD]
[TD]Southwest[/TD]
[TD]27/05/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Apples[/TD]
[TD]West[/TD]
[TD]30/06/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Orange[/TD]
[TD]Southwest[/TD]
[TD]01/06/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Bananas[/TD]
[TD]Southwest[/TD]
[TD]01/07/18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Beans[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have the following formula which works except if an item in a list from doesn't have a Exp
Date and other items from that location do, the status always shows "READY" the formula
wont realise to ***** the other dates in that location.

=IF(COUNTIF(Table1[Location],E2)=0,"No Inventory",IF(COUNTIFS(Table1[Location],
E2,Table1[Exp Date],""),"READY",IF(MIN(IF((Table1[Location]=E2)
*(Table1[Exp Date]<>""),Table1[Exp Date]))<today()+15,"urgent",
IF(MIN(IF((Table1[Location]=E2)*(Table1[Exp Date]<>""),Table1[Exp Date]))
<today()+90,"required",if(countifs(table1[location],d2,
Table1[Exp Date],""),"READY","READY"))))) </today()+90,"required",if(countifs(table1[location],d2,
</today()+15,"urgent",

 

[Oscar Cronquist]

Try this in cell D3 with my values above:

=IF(OR(D2=$A$2:$A$9),IFNA(INDEX($F$6:$F$8,MATCH(INDEX($B$2:$B$9,MATCH(D2,$A$2:$A$9,0)),$E$6:$E$8,1)),"Ready"),"No stock")

 

[Oscar Cronquist]

The formula is an array formula, I forgot to mention that.