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

LRATOZ

Board Regular
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
ABCDEFGH
2The highest total amount of rainfall for:JanFebMar
3The greatest total for the monthValueValueValue
4Happened in this yearYearYearYear
5
6Expected resultsJanFebMar
7423973
8201920202018
91/01/20182
102/01/20184
113/01/20188
124/01/20183
135/01/2018219
141/02/20187
152/02/20182
163/02/20185
174/02/20183
185/02/2018320
191/03/20189
202/03/201819
213/03/201824
224/03/201821
235/03/2018073
241/01/20193
252/01/201912
263/01/20199
274/01/201912
285/01/2019642
291/02/20190
302/02/20190
313/02/20195
324/02/20196
335/02/20191223
341/03/201914
352/03/20190
363/03/201923
374/03/20192
385/03/2019544
391/01/20200
402/01/20200
413/01/20202
424/01/20204
435/01/20202127
441/02/202021
452/02/20201
463/02/202014
474/02/20200
485/02/2020339
491/03/20207
502/03/20206
513/03/20205
524/03/20200
535/03/2020321
Sheet2
Cell Formulas
RangeFormula
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)
Named Ranges
NameRefers ToCells
Values=Sheet2!\$B\$9:\$B\$53C13

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.
Luke

Peter_SSs

MrExcel MVP, Moderator
You're welcome. Glad we could help.

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Replies
18
Views
249
Replies
1
Views
97
Replies
1
Views
64
Replies
2
Views
277
Replies
3
Views
159

1,141,575
Messages
5,707,173
Members
421,495
Latest member
jono_oh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back