MAX number of a cell if cell is in specific date range

[paun_shotts]

Hi,

Firstly, MAXIFS would work, but I don't have Office 360, I'm running Office 2013.
I need to find out the MAX number of column L if the date in column G is >= 2021,7,1 and <= 2022,6,30 (Financial year)
I am currently just using the MAX function, but It required me to update the range at the end of the financial year "=MAX('Supplier Evaluation Log'!L67:L128)" I dont want to have to remember to update this.
I have used AVERAGEIFS and SUMIFS and COUNTIFS for my other needs as per below:
=IFERROR(AVERAGEIFS('Supplier Evaluation Log'!J:J,'Supplier Evaluation Log'!G:G,">="&DATE(2021,7,1),'Supplier Evaluation Log'!G:G,"<="&DATE(2022,6,30)),"0")

I have read that this can be done with =AGGREGATE as well as an array of =MAX(IF(TEXT... but I am not able to get this to work.
Can someone please help me out?

I need to find out the MAX number of column L
If the date in column G is >= 2021,7,1 and <= 2022,6,30 (Financial year)

 

[Tetra201]

See if the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter) works for you:

=MAX(IF((G2:G32>=DATE(2021,7,1))*(G2:G32<DATE(2022,7,1)),L2:L32))

 

[Peter_SSs]

.. or using AGGREGATE as mentioned.
(I would recommend against using whole column references like you have in at least one formula above)

21 11 05.xlsm
	G	H	I	J	K	L	M	N
1
2	11/09/2020					26		86
3	22/09/2020					14
4	1/11/2020					6
5	10/12/2020					76
6	25/12/2020					15
7	13/03/2021					52
8	24/04/2021					7
9	30/08/2021					35
10	12/01/2022					1
11	17/01/2022					86
12	27/04/2022					60
13	1/05/2022					47
14	6/06/2022					75
15	2/07/2022					30
16	24/08/2022					90
17	2/09/2022					22
18	30/09/2022					79
19	14/11/2022					17
20	21/12/2022					78
21	31/12/2022					5
MAX IF

Cell Formulas
Range	Formula
N2	N2	=AGGREGATE(14,6,L$2:L$2000/((G$2:G$2000>=DATE(2021,7,1))*(G$2:G$2000<=DATE(2022,6,30))),1)

 

[paun_shotts]

Hi,

Thank you both so much! Both methods work perfectly.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[paun_shotts]

You're welcome. Glad we could help. Thanks for the follow-up.

Whats your reasoning for "(I would recommend against using whole column references like you have in at least one formula above)"

 

[Peter_SSs]

Whats your reasoning for "(I would recommend against using whole column references like you have in at least one formula above)"

When you specify a whole column reference, many functions will process all 1,048,576 cells for each column reference. That puts a significant burden on your machines calculation resources and can slow your sheet's performance noticeably.

As examples,
I put my formula (which checks 1,999 rows) in 20 cells and then my same formula but with whole column references and timed the recalculation of each group of 20 cells.
The 20 with full column references took 2.0015 seconds to re-calculate.
The 20 with the restricted rows took 0.00985 seconds to recalculate.

I then put Tetra201's MAX(IF formula (but changed it to also calculate over 1,999 rows) in 20 cells and the same formula with whole column references
The 20 with full column references took 1.9488 seconds to re-calculate.
The 20 with the restricted rows took 0.0092 seconds to recalculate.

So, on that size range with those formulas, the whole column reference formulas take more than 200 times the calculation time of the restricted ones.

 

[Tetra201]

I, in turn, would recommend against using the "<=" when working with dates, and here is why:

It is not uncommon that dates are entered as date-and-time (especially when the data come from outside automated sources) but formatted as date-only. In your case, if there is a datapoint where date is entered as, say, "30/06/2022 11:11", a formula with a "<=DATE(2022,6,30)" will miss it because "DATE(2022,6,30)" evaluates to "30/06/2022 0:00". That’s why I used a "<DATE(2022,7,1)" in my formula in Post #2.