Find Max of Year

[Pestomania]

Hello,

I am trying to find the "max" per year. I have this formula, but it returns value of 1 even when the max is 0 and doesn't define the year appropriately. This formula works when it is "sumproduct" but not when it is max.. It does return the appropriate number whenever there is a value greater than 0 in the selection.

=MAX($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))

10 Year Capacity Planning Model Rev 20240321 - Full_Morin_Densification.xlsm
	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
10		2023	2024	2025
11	Max	39	39	39
12	Sum	12	37	88
13
14		1/1/2023	2/1/2023	3/1/2023	4/1/2023	5/1/2023	6/1/2023	7/1/2023	8/1/2023	9/1/2023	10/1/2023	11/1/2023	12/1/2023	1/1/2024	2/1/2024	3/1/2024	4/1/2024	5/1/2024	6/1/2024	7/1/2024	8/1/2024	9/1/2024	10/1/2024	11/1/2024	12/1/2024	1/1/2025	2/1/2025	3/1/2025	4/1/2025	5/1/2025	6/1/2025	7/1/2025	8/1/2025	9/1/2025	10/1/2025	11/1/2025	12/1/2025
15		2	0	0	10	0	0	0	0	0	0	0	0	4	0	0	6	0	0	0	0	0	12	15	0	0	0	39	0	14	0	35	0	0	0	0	0
Proposed Equipment Add

Cell Formulas
Range	Formula
G11:I11	G11	=MAX($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))
G12:I12	G12	=SUMPRODUCT($G$15:$AP$15,--(YEAR($G$14:$AP$14)=G$10))

 

[bobsan42]

Try this:

=MAX($G$15:$AP$15*(YEAR($G$14:$AP$14)=G$10))

 

[Fluff]

How about

=MAX(FILTER($G$15:$AP$15,YEAR($G$14:$AP$14)=G$10))

 

[Pestomania]

How about

=MAX(FILTER($G$15:$AP$15,YEAR($G$14:$AP$14)=G$10))

This worked, thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Pestomania]

Glad we could help & thanks for the feedback.

I added an indirect (which works when not in a table) but breaks once it is in a table, any thoughts?

=MAX(FILTER(INDIRECT("Morin_"&$A5&"_Equipment_Proposed"),YEAR(Capacity_Charts_Date_Range)=C$1))-SUM($B5:B5)

 

[Fluff]

In what way doesn't it work?

 

[Pestomania]

In what way doesn't it work?

I receive a "#Calc" error stating the error is "Empty Array".

I apologize but can't show the entire table:

 

[Fluff]

That suggests that there is nothing matching the criteria.

 

[Pestomania]

That suggests that there is nothing matching the criteria.

How would that be if it works whenever it is outside of a table?

It provides results whenever not in a table:

But as soon as I turn that range into a table, it stops working. I can work with it and just work on different methods, but tables look nice.