# 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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABC
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/20190
263/01/20192
274/01/20191
285/01/2019612
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/202013
413/01/20202
424/01/20204
435/01/2020019
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
Rainfall
Cell Formulas
RangeFormula
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)

#### LRATOZ

##### Board Regular
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!
Luke

#### Peter_SSs

##### MrExcel MVP, Moderator
In that case I would like to show the earliest value!
OK, try these.

21 06 10.xlsm
ABCDEFGH
1
2JanFebMar
3193973
4201820202018
5
6
7
8
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/20190
263/01/20192
274/01/20191
285/01/2019612
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/202013
413/01/20202
424/01/20204
435/01/2020019
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
Rainfall
Cell Formulas
RangeFormula
F3:H3F3=AGGREGATE(14,6,\$C9:\$C53/(TEXT(\$A9:\$A53,"mmm")=F2),1)
F4:H4F4=AGGREGATE(15,6,YEAR(\$A9:\$A53)/((\$C\$9:\$C53=F3)*(TEXT(\$A9:\$A53,"mmm")=F2)),1)
C13,C53,C48,C43,C38,C33,C28,C23,C18C13=SUM(B9:B13)

#### LRATOZ

##### Board Regular

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

##### MrExcel MVP, Moderator
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

##### Well-known Member

See post #10 in

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
ABCDEF
1DateMonthTemperatureMax MoTotal CMax Date
2
31-Jan-2112025683-Jan-21
43-Jan-2112525683-Jan-21
55-Jan-2112325683-Jan-21
62-Feb-21221246719-Feb-21
717-Feb-21222246719-Feb-21
819-Feb-21224246719-Feb-21
94-Mar-2132329766-Mar-21
106-Mar-2132929766-Mar-21
1119-Mar-2132429766-Mar-21
123-Apr-2142532835-Apr-21
135-Apr-2143232835-Apr-21
1418-Apr-2142632835-Apr-21
153-May-21527285518-May-21
1618-May-21528285518-May-21
175-Jan-22131516317-Jan-22
187-Jan-22131616317-Jan-22
192-Feb-22142727642-Feb-22
205-Feb-22141827642-Feb-22
2122-Feb-22141927642-Feb-22
223-Mar-22153140715-Mar-22
235-Mar-22154040715-Mar-22
24
Data
Cell Formulas
RangeFormula
D3:D23D3=AGGREGATE(14,6,\$C\$3:\$C\$23/(\$B\$3:\$B\$23=B3),1)
E3:E23E3=SUMIFS(\$C\$3:\$C\$23,\$B\$3:\$B\$23,B3)
F3:F23F3=SUMPRODUCT(--(\$B\$3:\$B\$23=B3),--(\$C\$3:\$C\$23=D3),\$A\$3:\$A\$23)
B3:B23B3=MONTH(A3)+(YEAR(A3)-YEAR(\$A\$3))*12

T202105a.xlsm
ABCDEFG
1
2 --- Month ---Max °CDateRain sum
3January1Jan-21253-Jan-2168
4February2Feb-212419-Feb-2167
5March3Mar-21296-Mar-2176
6April4Apr-21325-Apr-2183
7May5May-212818-May-2155
8January13Jan-22167-Jan-2231
9February14Feb-22272-Feb-2264
10March15Mar-22405-Mar-2271
11
12
13
14
15By Month for all yearsMax °CDateRain Min Year
16January253-Jan-21312022
17February272-Feb-22642022
18March405-Mar-22712022
19
Report_a
Cell Formulas
RangeFormula
A3:A10A3=TEXT(C3,"mmmm")
B3:B10B3=MONTH(C3)+(YEAR(C3)-YEAR(\$C\$3))*12
D3:D10D3=INDEX(Data!\$D\$3:\$D\$23,MATCH(B3,Data!\$B\$3:\$B\$23))
E3:E10E3=INDEX(Data!\$F\$3:\$F\$23,MATCH(B3,Data!\$B\$3:\$B\$23))
F3:F10F3=LOOKUP(B3,Data!\$B\$3:\$B\$23,Data!\$E\$3:\$E\$23)
D16:D18D16=AGGREGATE(14,6,\$D\$3:\$D\$10/(\$A\$3:\$A\$10=A16),1)
E16:E18E16=AGGREGATE(14,6,\$E\$3:\$E\$10/(\$D\$3:\$D\$10=D16)*(\$A\$3:\$A\$10=A16),1)
F16:F18F16=AGGREGATE(15,6,\$F\$3:\$F\$10/(\$A\$3:\$A\$10=A16),1)
G16:G18G16=YEAR(AGGREGATE(15,6,\$C\$3:\$C\$10/(\$F\$3:\$F\$10=F16)*(\$A\$3:\$A\$10=A16),1))

#### Dave Patton

##### Well-known Member
Alternative without the monthly summary on the data sheet.

T202105a.xlsm
ABCDEFG
1Report_A
2 --- Month ---Max °CDateRain sum
3January1Jan-21253-Jan-2168
4February2Feb-212419-Feb-2167
5March3Mar-21296-Mar-2176
6April4Apr-21325-Apr-2183
7May5May-212818-May-2155
8January13Jan-22167-Jan-2231
9February14Feb-22272-Feb-2264
10March15Mar-22405-Mar-2271
11
12
13
14
15By Month for all yearsMax °CDateRain Min Year
16January253-Jan-21312022
17February272-Feb-22642022
18March405-Mar-22712022
19
Report_a
Cell Formulas
RangeFormula
F3:F10F3=SUMIFS(Data!\$C\$3:\$C\$23,Data!\$B\$3:\$B\$23,B3)
F16:F18F16=AGGREGATE(15,6,\$F\$3:\$F\$10/(\$A\$3:\$A\$10=A16),1)
G16:G18G16=YEAR(AGGREGATE(15,6,\$C\$3:\$C\$10/(\$F\$3:\$F\$10=F16)*(\$A\$3:\$A\$10=A16),1))

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCFGHIJKLMNOPQ
1
2JanFebMarAprMayJunJulAugSepOctNovDec
3359339374345388370370364358370360359
4200319721983199719691994196719962000199419631954
5
6
7
8
91/01/19544
102/01/195417
113/01/19541
124/01/19544
3426/01/195413
3527/01/195412
3628/01/19548
3729/01/195410
3830/01/19540
3931/01/195419258
401/02/195419
412/02/195413
423/02/195415
434/02/195415
6324/02/19549
6425/02/19548
6526/02/19544
6627/02/19547
6728/02/19545270
681/03/19547
692/03/19541
Rainfall
Cell Formulas
RangeFormula
F3:Q3F3=AGGREGATE(14,6,\$C9:\$C25000/(TEXT(\$A9:\$A25000,"mmm")=F2),1)
F4:Q4F4=AGGREGATE(15,6,YEAR(\$A9:\$A25000)/((\$C\$9:\$C25000=F3)*(TEXT(\$A9:\$A25000,"mmm")=F2)),1)
C9:C12,C34:C43,C63:C69C9=IF(MONTH(A9)=MONTH(A10),"",SUM(B\$9:B9)-SUM(C\$8:C8))

#### LRATOZ

##### Board Regular
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

Replies
18
Views
236
Replies
1
Views
96
Replies
2
Views
267
Replies
3
Views
150
Replies
5
Views
112

1,140,932
Messages
5,703,234
Members
421,285
Latest member
Bebek

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

### Which adblocker are you using?

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