Formula needed to find highest total for a given month over the years

[LRATOZ]

Hi,
I am still working on a dashboard for my weather data sheet.
I have rainfall data for each month for many years.
I would like to see in the dashboard the highest value for rainfall for each month and in which year it happened.
Something like this (Fictitious data):

Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
159	96	49	74	130	82	219	65	88	74
2020	2020	2014	2019	2015	2018	2018	2017	2019	2016

I made up a mock spreadsheet with some fictitious data. I had to limit the amount of cells as L2BB only allows for 300 cells maximum. But I hope you will get the gist to see what I mean.
I have listed 5 days of the first three months over three years to limit the amount of data. In real life I got data 365(6) days for each year and it's all in two columns: Dates and Rain gauge readings.
So, the function I'm after will need to calculate the total for each month, then compare the totals for that particular month over the years and then list the sum result for that month and show the year when it happened.
I hope this will make sense as it is not easy for me to put it in proper English (English is not my first language), sorry).
Here's a copy of that code:

2021-06-02 Weather test sheet.xlsx
	A	B	C	D	E	F	G	H
2	The highest total amount of rainfall for:					Jan	Feb	Mar
3	The greatest total for the month					Value	Value	Value
4	Happened in this year					Year	Year	Year
5
6	Expected results					Jan	Feb	Mar
7						42	39	73
8						2019	2020	2018
9	1/01/2018	2
10	2/01/2018	4
11	3/01/2018	8
12	4/01/2018	3
13	5/01/2018	2	19
14	1/02/2018	7
15	2/02/2018	2
16	3/02/2018	5
17	4/02/2018	3
18	5/02/2018	3	20
19	1/03/2018	9
20	2/03/2018	19
21	3/03/2018	24
22	4/03/2018	21
23	5/03/2018	0	73
24	1/01/2019	3
25	2/01/2019	12
26	3/01/2019	9
27	4/01/2019	12
28	5/01/2019	6	42
29	1/02/2019	0
30	2/02/2019	0
31	3/02/2019	5
32	4/02/2019	6
33	5/02/2019	12	23
34	1/03/2019	14
35	2/03/2019	0
36	3/03/2019	23
37	4/03/2019	2
38	5/03/2019	5	44
39	1/01/2020	0
40	2/01/2020	0
41	3/01/2020	2
42	4/01/2020	4
43	5/01/2020	21	27
44	1/02/2020	21
45	2/02/2020	1
46	3/02/2020	14
47	4/02/2020	0
48	5/02/2020	3	39
49	1/03/2020	7
50	2/03/2020	6
51	3/03/2020	5
52	4/03/2020	0
53	5/03/2020	3	21
Sheet2

Cell Formulas
Range	Formula
C13,C53,C48,C43,C38,C33,C28,C23,C18	C13	=SUM(B9:B13)

Named Ranges
Name	Refers To	Cells
Values	=Sheet2!$B$9:$B$53	C13

Obviously I wont' have the totals for each month in my datasheet as I want to keep the datasheet just for pure data and not for formulas or data manipulation. These totals are just there to give you an indication what the total should be.
I hope somebody can assist with this. I've been racking my brains for many days but I just haven't got enough skills to work this out.
I am using Excel 2016.
Many thanks for your help in advance!
Luke

 

[Peter_SSs]

I would like to see in the dashboard the highest value for rainfall for each month and in which year it happened.

What is to happen if the highest value for January occurred equally in 2 or more years? For example ..

21 06 10.xlsm
	A	B	C
9	1/01/2018	2
10	2/01/2018	4
11	3/01/2018	8
12	4/01/2018	3
13	5/01/2018	2	19
14	1/02/2018	7
15	2/02/2018	2
16	3/02/2018	5
17	4/02/2018	3
18	5/02/2018	3	20
19	1/03/2018	9
20	2/03/2018	19
21	3/03/2018	24
22	4/03/2018	21
23	5/03/2018	0	73
24	1/01/2019	3
25	2/01/2019	0
26	3/01/2019	2
27	4/01/2019	1
28	5/01/2019	6	12
29	1/02/2019	0
30	2/02/2019	0
31	3/02/2019	5
32	4/02/2019	6
33	5/02/2019	12	23
34	1/03/2019	14
35	2/03/2019	0
36	3/03/2019	23
37	4/03/2019	2
38	5/03/2019	5	44
39	1/01/2020	0
40	2/01/2020	13
41	3/01/2020	2
42	4/01/2020	4
43	5/01/2020	0	19
44	1/02/2020	21
45	2/02/2020	1
46	3/02/2020	14
47	4/02/2020	0
48	5/02/2020	3	39
49	1/03/2020	7
50	2/03/2020	6
51	3/03/2020	5
52	4/03/2020	0
53	5/03/2020	3	21
Rainfall

Cell Formulas
Range	Formula
C13,C53,C48,C43,C38,C33,C28,C23,C18	C13	=SUM(B9:B13)

 

[LRATOZ]

What is to happen if the highest value for January occurred equally in 2 or more years? For example ..

Yup, that's a possible problem. In that case I would like to show the earliest value!
Thanks for addressing this issue!
Luke

 

[Peter_SSs]

In that case I would like to show the earliest value!

OK, try these.

21 06 10.xlsm
	A	B	C	D	E	F	G	H
1
2						Jan	Feb	Mar
3						19	39	73
4						2018	2020	2018
5
6
7
8
9	1/01/2018	2
10	2/01/2018	4
11	3/01/2018	8
12	4/01/2018	3
13	5/01/2018	2	19
14	1/02/2018	7
15	2/02/2018	2
16	3/02/2018	5
17	4/02/2018	3
18	5/02/2018	3	20
19	1/03/2018	9
20	2/03/2018	19
21	3/03/2018	24
22	4/03/2018	21
23	5/03/2018	0	73
24	1/01/2019	3
25	2/01/2019	0
26	3/01/2019	2
27	4/01/2019	1
28	5/01/2019	6	12
29	1/02/2019	0
30	2/02/2019	0
31	3/02/2019	5
32	4/02/2019	6
33	5/02/2019	12	23
34	1/03/2019	14
35	2/03/2019	0
36	3/03/2019	23
37	4/03/2019	2
38	5/03/2019	5	44
39	1/01/2020	0
40	2/01/2020	13
41	3/01/2020	2
42	4/01/2020	4
43	5/01/2020	0	19
44	1/02/2020	21
45	2/02/2020	1
46	3/02/2020	14
47	4/02/2020	0
48	5/02/2020	3	39
49	1/03/2020	7
50	2/03/2020	6
51	3/03/2020	5
52	4/03/2020	0
53	5/03/2020	3	21
Rainfall

Cell Formulas
Range	Formula
F3:H3	F3	=AGGREGATE(14,6,$C9:$C53/(TEXT($A9:$A53,"mmm")=F2),1)
F4:H4	F4	=AGGREGATE(15,6,YEAR($A9:$A53)/(($C$9:$C53=F3)*(TEXT($A9:$A53,"mmm")=F2)),1)
C13,C53,C48,C43,C38,C33,C28,C23,C18	C13	=SUM(B9:B13)

 

[LRATOZ]

OK, try these.

Thanks Peter,
However slight problem:
The datasheet just has raw data in it (To keep it clean).
In my datasheet there's no sum of the values of each month.
I only added the totals for each month so that you don't need to do the calculations yourself.
So, say I want to find the maximum value for the month of January. The formula will need perform following tasks:
-Check for every month of January
-Make the sum of all values in each month of January
-Select the highest value of every month of January and print it.

My datasheet has many columns, but for simplicity reasons I only want to see the formula for the rain gauge.
Once I got a formula I can adapt it to every other dataset

The first column in the datasheet is the date (dd-mm-yy), the next column is Values from the rain gauge for each day.
So far, I've got data going back to 1954 up until today, so, about 22000 entries!
So that's why I want to create a formula that can look up the highest value for the rainfall of each month and then list that value and add the year in which it happened.
My apologies for the convoluted message.
Cheers,

Luke

 

[Peter_SSs]

I don't see that you will be able to do this without some sort of helper column(s). Even if it was possible, I think the calculation overhead would be more than with a helper column.

 

[Dave Patton]

See post #10 in
www.mrexcel.com/board/threads/how-to-find-the-maximum-value-in-a-column-based-on-month-and-year.1172458/

I did not enter the rainfall numbers. Since the numbers are not real numbers, assume column C is rain fall
Insert Column E

T202105a.xlsm
	A	B	C	D	E	F
1	Date	Month	Temperature	Max Mo	Total C	Max Date
2
3	1-Jan-21	1	20	25	68	3-Jan-21
4	3-Jan-21	1	25	25	68	3-Jan-21
5	5-Jan-21	1	23	25	68	3-Jan-21
6	2-Feb-21	2	21	24	67	19-Feb-21
7	17-Feb-21	2	22	24	67	19-Feb-21
8	19-Feb-21	2	24	24	67	19-Feb-21
9	4-Mar-21	3	23	29	76	6-Mar-21
10	6-Mar-21	3	29	29	76	6-Mar-21
11	19-Mar-21	3	24	29	76	6-Mar-21
12	3-Apr-21	4	25	32	83	5-Apr-21
13	5-Apr-21	4	32	32	83	5-Apr-21
14	18-Apr-21	4	26	32	83	5-Apr-21
15	3-May-21	5	27	28	55	18-May-21
16	18-May-21	5	28	28	55	18-May-21
17	5-Jan-22	13	15	16	31	7-Jan-22
18	7-Jan-22	13	16	16	31	7-Jan-22
19	2-Feb-22	14	27	27	64	2-Feb-22
20	5-Feb-22	14	18	27	64	2-Feb-22
21	22-Feb-22	14	19	27	64	2-Feb-22
22	3-Mar-22	15	31	40	71	5-Mar-22
23	5-Mar-22	15	40	40	71	5-Mar-22
24
Data

Cell Formulas
Range	Formula
D3:D23	D3	=AGGREGATE(14,6,$C$3:$C$23/($B$3:$B$23=B3),1)
E3:E23	E3	=SUMIFS($C$3:$C$23,$B$3:$B$23,B3)
F3:F23	F3	=SUMPRODUCT(--($B$3:$B$23=B3),--($C$3:$C$23=D3),$A$3:$A$23)
B3:B23	B3	=MONTH(A3)+(YEAR(A3)-YEAR($A$3))*12

T202105a.xlsm
	A	B	C	D	E	F	G
1
2	--- Month ---			Max °C	Date	Rain sum
3	January	1	Jan-21	25	3-Jan-21	68
4	February	2	Feb-21	24	19-Feb-21	67
5	March	3	Mar-21	29	6-Mar-21	76
6	April	4	Apr-21	32	5-Apr-21	83
7	May	5	May-21	28	18-May-21	55
8	January	13	Jan-22	16	7-Jan-22	31
9	February	14	Feb-22	27	2-Feb-22	64
10	March	15	Mar-22	40	5-Mar-22	71
11
12
13
14
15	By Month for all years			Max °C	Date	Rain Min	Year
16	January			25	3-Jan-21	31	2022
17	February			27	2-Feb-22	64	2022
18	March			40	5-Mar-22	71	2022
19
Report_a

Cell Formulas
Range	Formula
A3:A10	A3	=TEXT(C3,"mmmm")
B3:B10	B3	=MONTH(C3)+(YEAR(C3)-YEAR($C$3))*12
D3:D10	D3	=INDEX(Data!$D$3:$D$23,MATCH(B3,Data!$B$3:$B$23))
E3:E10	E3	=INDEX(Data!$F$3:$F$23,MATCH(B3,Data!$B$3:$B$23))
F3:F10	F3	=LOOKUP(B3,Data!$B$3:$B$23,Data!$E$3:$E$23)
D16:D18	D16	=AGGREGATE(14,6,$D$3:$D$10/($A$3:$A$10=A16),1)
E16:E18	E16	=AGGREGATE(14,6,$E$3:$E$10/($D$3:$D$10=D16)*($A$3:$A$10=A16),1)
F16:F18	F16	=AGGREGATE(15,6,$F$3:$F$10/($A$3:$A$10=A16),1)
G16:G18	G16	=YEAR(AGGREGATE(15,6,$C$3:$C$10/($F$3:$F$10=F16)*($A$3:$A$10=A16),1))

 

[Dave Patton]

Alternative without the monthly summary on the data sheet.

T202105a.xlsm
	A	B	C	D	E	F	G
1	Report_A
2	--- Month ---			Max °C	Date	Rain sum
3	January	1	Jan-21	25	3-Jan-21	68
4	February	2	Feb-21	24	19-Feb-21	67
5	March	3	Mar-21	29	6-Mar-21	76
6	April	4	Apr-21	32	5-Apr-21	83
7	May	5	May-21	28	18-May-21	55
8	January	13	Jan-22	16	7-Jan-22	31
9	February	14	Feb-22	27	2-Feb-22	64
10	March	15	Mar-22	40	5-Mar-22	71
11
12
13
14
15	By Month for all years			Max °C	Date	Rain Min	Year
16	January			25	3-Jan-21	31	2022
17	February			27	2-Feb-22	64	2022
18	March			40	5-Mar-22	71	2022
19
Report_a

Cell Formulas
Range	Formula
F3:F10	F3	=SUMIFS(Data!$C$3:$C$23,Data!$B$3:$B$23,B3)
F16:F18	F16	=AGGREGATE(15,6,$F$3:$F$10/($A$3:$A$10=A16),1)
G16:G18	G16	=YEAR(AGGREGATE(15,6,$C$3:$C$10/($F$3:$F$10=F16)*($A$3:$A$10=A16),1))

 

[Peter_SSs]

I don't see that you will be able to do this without some sort of helper column

Perhaps I misunderstood your comments about a helper column & you could just go with this in the helper column and use the formulas I suggested above for the monthly maximums and years?
I have hidden quite a few rows for this screen-shot.

21 06 10.xlsm
	A	B	C			F	G	H	I	J	K	L	M	N	O	P	Q
1
2						Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3						359	339	374	345	388	370	370	364	358	370	360	359
4						2003	1972	1983	1997	1969	1994	1967	1996	2000	1994	1963	1954
5
6
7
8
9	1/01/1954	4
10	2/01/1954	17
11	3/01/1954	1
12	4/01/1954	4
34	26/01/1954	13
35	27/01/1954	12
36	28/01/1954	8
37	29/01/1954	10
38	30/01/1954	0
39	31/01/1954	19	258
40	1/02/1954	19
41	2/02/1954	13
42	3/02/1954	15
43	4/02/1954	15
63	24/02/1954	9
64	25/02/1954	8
65	26/02/1954	4
66	27/02/1954	7
67	28/02/1954	5	270
68	1/03/1954	7
69	2/03/1954	1
Rainfall

Cell Formulas
Range	Formula
F3:Q3	F3	=AGGREGATE(14,6,$C9:$C25000/(TEXT($A9:$A25000,"mmm")=F2),1)
F4:Q4	F4	=AGGREGATE(15,6,YEAR($A9:$A25000)/(($C$9:$C25000=F3)*(TEXT($A9:$A25000,"mmm")=F2)),1)
C9:C12,C34:C43,C63:C69	C9	=IF(MONTH(A9)=MONTH(A10),"",SUM(B$9:B9)-SUM(C$8:C8))

 

[LRATOZ]

Perhaps I misunderstood your comments about a helper column & you could just go with this in the helper column and use the formulas I suggested above for the monthly maximums and years?
I have hidden quite a few rows for this screen-shot.

Peter it worked!
This is a great solution!
I checked for nearly every max value in my data collection and it's 100% spot on!
Fantastic job, I'm over the moon with this solution.
Admittingly it requires an extra column for the monthly totals but that's something I can live with.
This function will save me endless hours of sifting through the data.
You are my hero of the day! Thank you very much for your hard work.
Also a big thank you to Dave Patton and Maabadi for their contributions; Much appreciated guys! I wish I could do something in return.
Cheers,

Luke