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

LRATOZ

Board Regular
Joined
Aug 17, 2014
Messages
57
Office Version
  1. 2016
Platform
  1. Windows
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):

JanFebMarAprMayJunJulAugSepOct
15996497413082219658874
2020202020142019201520182018201720192016

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.
Many thanks for your help in advance!
Luke
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Glad we could help. :)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Forum statistics

Threads
1,136,864
Messages
5,678,213
Members
419,751
Latest member
richkings

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top