How to Wrap MINIFS around Filter Function

[MixedUpExcel]

Hi,

I have a range with dates in - a table of 20 cells by 15 high. The dates are not sequential but usually either 7 days apart or 14 days apart.

I have a reference in the column immediately to the left of the dates which looks like this:

63MON

63MON1

63MON2

63TUE

63TUE1

63TUE2

63WED

63WED1

63WED2

63THU

63THU1

63THU2

63FRI

63FRI1

63FRI2

I run a report with lots of data and order dates.

I want to find the row which coincides with the reference above (eg. 63MON1 and then I want to find the next available date after the order date in the 20 x 15 table of dates (in the row for 63MON1)

i can get MINIFS to work if I have just 1 row eg. =MINIFS(B2:U2,B2:U2,">"&D23) where D23 contains the date

I want to be able to find which Row.. so I used the FILTER function eg. =FILTER(B2:U17,A2:A17="63MON1") - this is great, it gives me all of the dates in the row of the table that I need.

I can't get MINIFS and FILTER to work together.

I've tried: =MINIFS(FILTER(B2:U17,A2:A17="63MON1"),FILTER(B2:U17,A2:A17="63MON1"),">"&D23)

The above makes sense to me as it follows the structure of the 2 Functions.. but it just doesn't work.

Is it the structure or are these 2 just not meant to work together and is there another way?

Thanks in advance for your help / advice.

Simon

 

[awoohaw]

Simon,
What column has the order date? Are any of the 20 columns of dates to be ignored? Posting a sample (just 5 rows and 5 columns) would help the forum help you.

 

[Fluff]

Minifs needs a range not an array which is what filter returns.
Try

=LET(x,XLOOKUP("63MON1",A2:A17,B2:U17),MINIFS(x,x,">="&D23))

 

[MixedUpExcel]

Simon,
What column has the order date? Are any of the 20 columns of dates to be ignored? Posting a sample (just 5 rows and 5 columns) would help the forum help you.

Hi,

Hope this helps a bit more.

I'm also playing around with Xlookup to see if that might offer a solution.

So a recap.

I want to locate the row which 63MON1 is in - in this case Row 3
I want to find the next date after the order date. The order date is 07/03/2024. The next date after the order date in the table on Row 3 would be 18/03/2024.
Apologies as this is the English date structure
The report I run will have approx.. 3000 rows on it and I want to put this formula in a cell against each row on the report.
I can fine tune it after, just want to try and get it working on a simpler form first.

My report would be on another sheet, in the format of:

Delivery Date	Run Week	Product	Order Date	more data….
XXXXXX	63MON1	ABC123	07/03/2024	abcdef

And the main data table for the formula to extract the information would be like below:

ROW	A	B	C	D	E	F	G	H	I
2	63MON	05/02/2024​	12/02/2024​	19/02/2024​	26/02/2024​	04/03/2024​	11/03/2024​	18/03/2024​	25/03/2024​
3	63MON1	05/02/2024​	19/02/2024​	04/03/2024​	18/03/2024​	01/04/2024​	15/04/2024​	29/04/2024​	13/05/2024​
4	63MON2	12/02/2024​	26/02/2024​	11/03/2024​	25/03/2024​	08/04/2024​	22/04/2024​	06/05/2024​	20/05/2024​
5	63TUE	06/02/2024​	13/02/2024​	20/02/2024​	27/02/2024​	05/03/2024​	12/03/2024​	19/03/2024​	26/03/2024​
6	63TUE1	06/02/2024​	20/02/2024​	05/03/2024​	19/03/2024​	02/04/2024​	16/04/2024​	30/04/2024​	14/05/2024​
7	63TUE2	13/02/2024​	27/02/2024​	12/03/2024​	26/03/2024​	09/04/2024​	23/04/2024​	07/05/2024​	21/05/2024​
8	63WED	07/02/2024​	14/02/2024​	21/02/2024​	28/02/2024​	06/03/2024​	13/03/2024​	20/03/2024​	27/03/2024​
9	63WED1	07/02/2024​	21/02/2024​	06/03/2024​	20/03/2024​	03/04/2024​	17/04/2024​	01/05/2024​	15/05/2024​
10	63WED2	14/02/2024​	28/02/2024​	13/03/2024​	27/03/2024​	10/04/2024​	24/04/2024​	08/05/2024​	22/05/2024​
11	63THU	08/02/2024​	15/02/2024​	22/02/2024​	29/02/2024​	07/03/2024​	14/03/2024​	21/03/2024​	28/03/2024​
12	63THU1	08/02/2024​	22/02/2024​	07/03/2024​	21/03/2024​	04/04/2024​	18/04/2024​	02/05/2024​	16/05/2024​
13	63THU2	15/02/2024​	29/02/2024​	14/03/2024​	28/03/2024​	11/04/2024​	25/04/2024​	09/05/2024​	23/05/2024​
14	63FRI	09/02/2024​	16/02/2024​	23/02/2024​	01/03/2024​	08/03/2024​	15/03/2024​	22/03/2024​	29/03/2024​
15	63FRI1	09/02/2024​	23/02/2024​	08/03/2024​	22/03/2024​	05/04/2024​	19/04/2024​	03/05/2024​	17/05/2024​
16	63FRI2	16/02/2024​	01/03/2024​	15/03/2024​	29/03/2024​	12/04/2024​	26/04/2024​	10/05/2024​	24/05/2024​
17
18
19
20
21
22	Row Ref	Next After	Result
23	63MON1	07/03/2024	18/03/2024

Thanks again for any help.

Simon

 

[awoohaw]

Hi,

Hope this helps a bit more.

I'm also playing around with Xlookup to see if that might offer a solution.

So a recap.

I want to locate the row which 63MON1 is in - in this case Row 3
I want to find the next date after the order date. The order date is 07/03/2024. The next date after the order date in the table on Row 3 would be 18/03/2024.
Apologies as this is the English date structure
The report I run will have approx.. 3000 rows on it and I want to put this formula in a cell against each row on the report.
I can fine tune it after, just want to try and get it working on a simpler form first.

ROW	A	B	C	D	E	F	G	H	I
2	63MON	05/02/2024​	12/02/2024​	19/02/2024​	26/02/2024​	04/03/2024​	11/03/2024​	18/03/2024​	25/03/2024​
3	63MON1	05/02/2024​	19/02/2024​	04/03/2024​	18/03/2024​	01/04/2024​	15/04/2024​	29/04/2024​	13/05/2024​
4	63MON2	12/02/2024​	26/02/2024​	11/03/2024​	25/03/2024​	08/04/2024​	22/04/2024​	06/05/2024​	20/05/2024​
5	63TUE	06/02/2024​	13/02/2024​	20/02/2024​	27/02/2024​	05/03/2024​	12/03/2024​	19/03/2024​	26/03/2024​
6	63TUE1	06/02/2024​	20/02/2024​	05/03/2024​	19/03/2024​	02/04/2024​	16/04/2024​	30/04/2024​	14/05/2024​
7	63TUE2	13/02/2024​	27/02/2024​	12/03/2024​	26/03/2024​	09/04/2024​	23/04/2024​	07/05/2024​	21/05/2024​
8	63WED	07/02/2024​	14/02/2024​	21/02/2024​	28/02/2024​	06/03/2024​	13/03/2024​	20/03/2024​	27/03/2024​
9	63WED1	07/02/2024​	21/02/2024​	06/03/2024​	20/03/2024​	03/04/2024​	17/04/2024​	01/05/2024​	15/05/2024​
10	63WED2	14/02/2024​	28/02/2024​	13/03/2024​	27/03/2024​	10/04/2024​	24/04/2024​	08/05/2024​	22/05/2024​
11	63THU	08/02/2024​	15/02/2024​	22/02/2024​	29/02/2024​	07/03/2024​	14/03/2024​	21/03/2024​	28/03/2024​
12	63THU1	08/02/2024​	22/02/2024​	07/03/2024​	21/03/2024​	04/04/2024​	18/04/2024​	02/05/2024​	16/05/2024​
13	63THU2	15/02/2024​	29/02/2024​	14/03/2024​	28/03/2024​	11/04/2024​	25/04/2024​	09/05/2024​	23/05/2024​
14	63FRI	09/02/2024​	16/02/2024​	23/02/2024​	01/03/2024​	08/03/2024​	15/03/2024​	22/03/2024​	29/03/2024​
15	63FRI1	09/02/2024​	23/02/2024​	08/03/2024​	22/03/2024​	05/04/2024​	19/04/2024​	03/05/2024​	17/05/2024​
16	63FRI2	16/02/2024​	01/03/2024​	15/03/2024​	29/03/2024​	12/04/2024​	26/04/2024​	10/05/2024​	24/05/2024​
17
18
19
20
21
22	Row Ref	Next After	Result
23	63MON1		07/03/2024		18/03/2024

Thanks again for any help.

Simon

Thanks, for the additional information. But @Fluff has provided you with the solution. I was thinking to hard.

 

[MixedUpExcel]

Minifs needs a range not an array which is what filter returns.
Try

=LET(x,XLOOKUP("63MON1",A2:A17,B2:U17),MINIFS(x,x,">="&D23))

Hi Fluff

Unfortunately, I don't understand your formula and can't get it to work.
- It's the LET that I don't understand.

Do I need to replace the x with something?

However, I have been able to work out a solution:

=XLOOKUP(E23,FILTER(C2:J16,B2:B16=C23),FILTER(C2:J16,B2:B16=C23),,1)

I'd still like to understand your solution though.

Thanks.

Simon

 

[Fluff]

Do I need to replace the x with something?

No, you just use the formula as-is.

In what way didn't it work?

 

[MixedUpExcel]

No, you just use the formula as-is.

In what way didn't it work?

Apologies Fluff,

I'd changed the range references in your formula to suit my range... and I'd not done it accurately.

It does work.. thank you.

Now I have 2 options.

Thanks again for your time.

Simon

 

[Fluff]

Glad we could help & thanks for the feedback.