Find First and Last Period in 2D Table

[excelstudy11]

I'm trying to remember how to best structure a formula to calculate the first date of a project's incomes and the last date in a large multi-dimensional table.

It's been a while since I've had to structure data in this format and tackle a need like this, and am hitting a wall on the right approach to realize the min and max array positions based on the interior value of the table.

I am attaching a sample view. To structure the data I have a named array for Data_Months(C2:N2), Data_Projects (A3:A6), Data_Status (B3:B6) and Data_Values (C3:N6)

I feel like there should be a way to get to this via nested conditional xlookup or old-school index match to find the min/max array value in Data_Months where Data_Projects = Project Name Reference (from output table) and where Data_Value > 0 (and add in Project Status = Reference from Output Table to test logic with additional reference parameters since I will need to layer more in)

I just am tripping myself up on how to create that array of Data_Month indexes where Project = True and Value > 0 to then process in-formula (ie not creating output arrays to parse in a 2nd step).

Let me know if this makes sense and if anyone can help remind me how to best do this.

 

[Peter_SSs]

Welcome to the MrExcel board!

I have not used any of your named ranges, but see if you can use any of this as a start.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

23 11 16.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan 2023	Feb 2023	Mar 2023	Apr 2023	May 2023	Jun 2023	Jul 2023	Aug 2023	Sep 2023	Oct 2023	Nov 2023	Dec 2023
2	Project A	Status X					10,000	10,000	10,000	10,000	10,000
3	Project B	Status Y		18,000	18,000	18,000	18,000	18,000	18,000	18,000
4	Project C	Status X									12,000	12,000	12,000	12,000
5	Project D	Status X	22,000	22,000	22,000	22,000	22,000	22,000	22,000	22,000	22,000	22,000	22,000
6
7
8
9		Start	End	Total
10	Project A	May-23	Sep-23	50,000
11	Project B			0
12	Project C	Sep-23	Dec-23	48,000
13	Project D	Jan-23	Nov-23	242,000
Projects

Cell Formulas
Range	Formula
B10:B13	B10	=IF(XLOOKUP(A10,A$2:A$5,B$2:B$5)="Status X",MINIFS(C$1:N$1,INDEX(C$2:N$5,MATCH(A10,A$2:A$5,0),0),"<>"),"")
C10:C13	C10	=IF(XLOOKUP(A10,A$2:A$5,B$2:B$5)="Status X",MAXIFS(C$1:N$1,INDEX(C$2:N$5,MATCH(A10,A$2:A$5,0),0),"<>"),"")
D10:D13	D10	=IF(C10="",0,SUM(FILTER(C$2:N$5,A$2:A$5=A10)))

 

[excelstudy11]

Thanks for the reply, that worked!

Appreciate the help, the minifs and index/match nesting is great.

As for XL2BB - can't install add-ons to our excel due to sensitive nature of data we access, so stuck with sample screenshots or workbooks.

 

[Alex Blakenburg]

An alternative approach would be to use a 2D Xlookup.
The advantage of this would be that by not relying on Min and Max it would still work if you converted the Table into an actual Excel Table at which point all the date headings would become text and Min/Max would not work without doing a conversion.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23
2	Project A	Status X					10000	10000	10000	10000	10000
3	Project B	Status Y		18000	18000	18000	18000	18000	18000	18000
4	Project C	Status X									12000	12000	12000	12000
5	Project D	Status X	22000	22000	22000	22000	22000	22000	22000	22000	22000	22000	22000
6
7
8
9		Start	End	Total
10	Project A	May-23	Sep-23	50000
11	Project B			0
12	Project C	Sep-23	Dec-23	48000
13	Project D	Jan-23	Nov-23	242000
XLookup 2D

Cell Formulas
Range	Formula
B10:B13	B10	=XLOOKUP(TRUE,XLOOKUP(1,($A$2:$A$5=$A10)*($B$2:$B$5="Status X"),$C$2:$N$5,"")<>"",$C$1:$N$1,"")
C10:C13	C10	=XLOOKUP(TRUE,XLOOKUP(1,($A$2:$A$5=$A10)*($B$2:$B$5="Status X"),$C$2:$N$5,"")<>"",$C$1:$N$1,"",0,-1)
D10:D13	D10	=IF(C10="",0,SUM(FILTER(C$2:N$5,A$2:A$5=A10)))

 

[Peter_SSs]

As for XL2BB - can't install add-ons to our excel

Fair enough. If you start any new threads in the future, state that in post #1 so we don't keep asking you.

so stuck with sample screenshots or workbooks.

If the data is not too big, another option is to simply copy/paste from your Excel to the forum. If you do that you need to explain what range you have posted and also tell us any relevant formulas and where they are. But at least we can copy from that to test whereas we cannot from an image.