How to find the maximum value in a column based on month and year?

[LRATOZ]

Hi,

I have been collecting weather data for six years and I would like to make a dashboard that shows me the following:

The highest recorded temperature per month for each year.
(Similar as the record values in the climate table at Climate of Melbourne - Wikipedia)

My spreadsheet has got a column showing every dat over the last six years and has also a temperature reading for each date.

So, I want to find the highest temperature per month and per year with the date when it happened.
To find the maximum value per column is easy but I would like to see the maximum value for each month and on what date and in which year it happened.

Obviously, I record much more the just the temperature but once I know how to do this then it will be easy to apply the same formula to different data types.
So, if somebody has a solution this I would be grateful to hear about it.
Thank you very much in advance!
Luke

 

[maabadi]

1. Please Update Your Account Details to We know which version of Excel you use to we help you based on.
At The Newer Version you can Use MAXIFS function.
2. Please Update example file with XL2BB ADDIN (Preferable) or upload at Free Hosting Site e.g. www.dropbox.com, GoogleDrive or .... and Insert Link Here.

 

[Dave Patton]

T202105a.xlsm
	A	B	C	D	E	F	G	H	I
1	Date		Temperature					Max if
2					Start	End	Max		Date Max
3	1-Jan-21		20		1-Jan-21	31-Jan-21	25	25	3-Jan-21
4	3-Jan-21		25		Feb-21	28-Feb-21	24	24	19-Feb-21
5	5-Jan-21		23		Mar-21	28-Mar-21	29	29	6-Mar-21
6	2-Feb-21		21		Apr-21	28-Apr-21	32	32	5-Apr-21
7	17-Feb-21		22		May-21	28-May-21	28	28	18-May-21
8	19-Feb-21		24
9	4-Mar-21		23
10	6-Mar-21		29		For Year
11	19-Mar-21		24		1-Jan-21	31-Dec-21	32	32	5-Apr-21
12	3-Apr-21		25
13	5-Apr-21		32
14	18-Apr-21		26
15	3-May-21		27
16	18-May-21		28
17	5-May-22		55
5a

Cell Formulas
Range	Formula
G3:G7	G3	=MAXIFS(Temp,Dates,">="&E3,Dates,"<="&EOMONTH(E3,0))
H11,H3:H7	H3	=MAXIFS(Temp,Dates,">="&E3,Dates,"<="&F3)
I3:I7	I3	=SUMPRODUCT(--(Dates>=E3),--(Dates<=F3),--(Temp=G3),Dates)
E4:F7	E4	=EDATE(E3,1)
A4:A5,A10,A13	A4	=+A3+2
A7,A12,A15:A16	A7	=A6+15
G11	G11	=MAXIFS(Temp,Dates,">="&E11,Dates,"<="&EOMONTH(E11-1,12))
I11	I11	=SUMPRODUCT(--(Dates>=E11),--(Dates<=F11),--(Temp=H11),Dates)
A9,A11,A14	A9	=A7+15

Named Ranges
Name	Refers To	Cells
Dates	='5a'!$A$3:$A$17	G11:I11, G3:I7, A4
Temp	='5a'!$C$3:$C$17	G11:I11, G3:I7

 

[LRATOZ]

T202105a.xlsm
	A	B	C	D	E	F	G	H	I
1	Date		Temperature					Max if
2					Start	End	Max		Date Max
3	1-Jan-21		20		1-Jan-21	31-Jan-21	25	25	3-Jan-21
4	3-Jan-21		25		Feb-21	28-Feb-21	24	24	19-Feb-21
5	5-Jan-21		23		Mar-21	28-Mar-21	29	29	6-Mar-21
6	2-Feb-21		21		Apr-21	28-Apr-21	32	32	5-Apr-21
7	17-Feb-21		22		May-21	28-May-21	28	28	18-May-21
8	19-Feb-21		24
9	4-Mar-21		23
10	6-Mar-21		29		For Year
11	19-Mar-21		24		1-Jan-21	31-Dec-21	32	32	5-Apr-21
12	3-Apr-21		25
13	5-Apr-21		32
14	18-Apr-21		26
15	3-May-21		27
16	18-May-21		28
17	5-May-22		55
5a

Cell Formulas
Range	Formula
G3:G7	G3	=MAXIFS(Temp,Dates,">="&E3,Dates,"<="&EOMONTH(E3,0))
H11,H3:H7	H3	=MAXIFS(Temp,Dates,">="&E3,Dates,"<="&F3)
I3:I7	I3	=SUMPRODUCT(--(Dates>=E3),--(Dates<=F3),--(Temp=G3),Dates)
E4:F7	E4	=EDATE(E3,1)
A4:A5,A10,A13	A4	=+A3+2
A7,A12,A15:A16	A7	=A6+15
G11	G11	=MAXIFS(Temp,Dates,">="&E11,Dates,"<="&EOMONTH(E11-1,12))
I11	I11	=SUMPRODUCT(--(Dates>=E11),--(Dates<=F11),--(Temp=H11),Dates)
A9,A11,A14	A9	=A7+15

Named Ranges
Name	Refers To	Cells
Dates	='5a'!$A$3:$A$17	G11:I11, G3:I7, A4
Temp	='5a'!$C$3:$C$17	G11:I11, G3:I7

Hi Dave,

Thank you very much for this!
Unfortunately, at the moment I'm overwhelmed with work commitments but will spend some time over the weekend.
I will definitly get back to you with feedback.
In the meantime thank you very much for your time and effort to help me out.
Much appreciated!
Luke

 

[LRATOZ]

Hi Dave,

I was able to find a couple of hours to go through your suggestions.
It took me a while but eventualy I was able to understand what you meant.
However, after three hours I got nowhere as the moment I press the enter button the cell showed #NAME?
I could't figure it out where I went wrong but then the penny dropped: I am using Excel 2016 and your code is for Excel 365.
Unfortunately the MAXIFS function is not included in the 2016 version.
I looked for possible code replacement and found a work-around by using something like this: MAX(IF(((A1:A10="value")*(C1:C10="value2"))>0, B1:B10))
Just when I understood your suggestinon it rendered useless for my version
I spent even more time trying to substitute the MAXIFS function with the suggestion as shown above, but unfrotunately I lack the knowledge to do so.
I followed the suggestion of Maabadi to update my profile so that next time everybody knows which version I use.
So, could you please replace your formula

=MAXIFS(Temp,Dates,">="&E3,Dates,"<="&EOMONTH(E3,0))

with a formula that works in Excel 2016?
My sincere apologies for not telling you which version I've got; I just didn't realize there was extra functionality in other versions.
Anyway, your suggestion is exactly what I'm looking for and I'm sure that this will be the solution to my problem.
Thank you very much for your help!
Cheers,
Luke

 

[LRATOZ]

1. Please Update Your Account Details to We know which version of Excel you use to we help you based on.
At The Newer Version you can Use MAXIFS function.
2. Please Update example file with XL2BB ADDIN (Preferable) or upload at Free Hosting Site e.g. www.dropbox.com, GoogleDrive or .... and Insert Link Here.

I've updated my profile Maabadi. Thanks for the suggestion!

 

[Dave Patton]

Good morning. It is 3:30 AM here. I may have better ideas later. You will have to determine what is appropriate for your database.
The helper columns can make the formula more manageable.

N.B. You can post my suggestion into a clean sheet; see the icon below the f(x) in the message header.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Month	Temperature	Max Mo
2					Start	End	Month	Max Temperature	Date
3	1-Jan-21	1	20	25	1-Jan-21	31-Jan-21	1	25	3-Jan-21
4	3-Jan-21	1	25	25	Feb-21	28-Feb-21	2	24	19-Feb-21
5	5-Jan-21	1	23	25	Mar-21	28-Mar-21	3	29	6-Mar-21
6	2-Feb-21	2	21	24	Apr-21	28-Apr-21	4	32	5-Apr-21
7	17-Feb-21	2	22	24	May-21	28-May-21	5	28	18-May-21
8	19-Feb-21	2	24	24
9	4-Mar-21	3	23	29
10	6-Mar-21	3	29	29
11	19-Mar-21	3	24	29
12	3-Apr-21	4	25	32
13	5-Apr-21	4	32	32
14	18-Apr-21	4	26	32
15	3-May-21	5	27	28
16	18-May-21	5	28	28
5aa

Cell Formulas
Range	Formula
H3:H7	H3	=LOOKUP(G3,$B$3:B$16,D$3:D$16)
I3:I7	I3	=SUMPRODUCT(--($B$3:$B$16=G3),--($C$3:$C$16=H3),$A$3:$A$16)
D3:D16	D3	=MAX(IF($B$3:$B$16=B3,$C$3:$C$16))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LRATOZ]

Good morning Dave,

Very impressive that you can work something out like that that early in the morning!
Over here it's nearly 8 PM and I will check it out tomorrow.
Anyway, my apologies to disturb your rest at night
Cheers,

luke

 

[Dave Patton]

An alternative

T202105a.xlsm
	A	B	C	D	E
1	Date	Month	Temperature	Max Mo	Max Date
2
3	1-Jan-21	1	20	25	3-Jan-21
4	3-Jan-21	1	25	25	3-Jan-21
5	5-Jan-21	1	23	25	3-Jan-21
6	2-Feb-21	2	21	24	19-Feb-21
7	17-Feb-21	2	22	24	19-Feb-21
8	19-Feb-21	2	24	24	19-Feb-21
9	4-Mar-21	3	23	29	6-Mar-21
10	6-Mar-21	3	29	29	6-Mar-21
11	19-Mar-21	3	24	29	6-Mar-21
12	3-Apr-21	4	25	32	5-Apr-21
13	5-Apr-21	4	32	32	5-Apr-21
14	18-Apr-21	4	26	32	5-Apr-21
15	3-May-21	5	27	28	18-May-21
16	18-May-21	5	28	28	18-May-21
17
5aaa

Cell Formulas
Range	Formula
D3:D16	D3	=AGGREGATE(14,6,$C$3:$C$16/($B$3:$B$16=B3),1)
E3:E16	E3	=SUMPRODUCT(--($B$3:$B$16=B3),--($C$3:$C$16=D3),$A$3:$A$16)

 

[Dave Patton]

Since the data should not change, I would convert the helper column ranges to values.
I left the formulas in first data row Row 3.
The reporting would probably be on another Sheet.
The Index/Match is probably faster/more efficient that the earlier suggestion.

T202105a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date	Month	Temperature	Max Mo	Max Date			Max if
2							Month		Max °C	Date
3	1-Jan-21	1	20	25	3-Jan-21		1	Jan-21	25	3-Jan-21
4	3-Jan-21	1	25	25	3-Jan-21		2	Feb-21	24	19-Feb-21
5	5-Jan-21	1	23	25	3-Jan-21		3	Mar-21	29	6-Mar-21
6	2-Feb-21	2	21	24	19-Feb-21		4	Apr-21	32	5-Apr-21
7	17-Feb-21	2	22	24	19-Feb-21		5	May-21	28	18-May-21
8	19-Feb-21	2	24	24	19-Feb-21
9	4-Mar-21	3	23	29	6-Mar-21
10	6-Mar-21	3	29	29	6-Mar-21
11	19-Mar-21	3	24	29	6-Mar-21
12	3-Apr-21	4	25	32	5-Apr-21
13	5-Apr-21	4	32	32	5-Apr-21
14	18-Apr-21	4	26	32	5-Apr-21
15	3-May-21	5	27	28	18-May-21
16	18-May-21	5	28	28	18-May-21
17
5aaa

Cell Formulas
Range	Formula
B3	B3	=MONTH(A3)+(YEAR(A3)-YEAR($A$3))*12
D3	D3	=AGGREGATE(14,6,$C$3:$C$16/($B$3:$B$16=B3),1)
E3	E3	=SUMPRODUCT(--($B$3:$B$16=B3),--($C$3:$C$16=D3),$A$3:$A$16)
I3	I3	=INDEX($D$3:$D$16,MATCH(G3,$B$3:$B$16))
J3	J3	=INDEX($E$3:$E$16,MATCH(G3,$B$3:$B$16))