Get the max sum of 2 or more consecutive values

sama9000

New Member
Joined
Aug 11, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I'm either looking for a formula solution or vba code to calculate the data on cells L2 and M2 ( I've tried multiple formulas and I cant accomplish what I need)

Explanation

I've a table from column range G:I, the table is registering the days from 1 to 1000, the value is the amount sold ( it can be a credit or debit) , when is a Credit, column "I" will display "Good Day", otherwise "Bad day" for debits. All of this calculation is being processed correctly, the problem comes when I want to get the max sum value for either the consecutive "Good Days" or "Bad Days"

Day 1 and Day 2 are consecutive "Bad Days" and the sum of both values is 200 ( so far this is the maximum sum value ), the problem comes when I get again consecutive "Bad Days" and the sum of those values is greater than my initial consecutive max sum values. Day 500 and 501 are consecutive "Bad Days" and the sum of both is 600 ( that is what I want in cell L2). The same process for "Good Day"

Consecutive days mean more than 1 day either being "Bad Day" or "Good Day" , example, 2 days,4 days, 100 days,etc. Let's say I've got 100 consecutive "Bad Days" with a sum of 1000 , then I got 1 "God Day" and then I got 2 consecutive "Bad Days" with a sum of 5000 which is greater than the one I've got in the 100 consecutive "Bad days" before ( I will need the 5000 value on cell L2)

On the picture below, the first 2 days are consecutive "Bad days" and the total sum is 200 ( so far this is the max value). From day 4 to day 10 there 7 consecutive "Bad Days" and the total sum is 35 ( which is less than the total of previous consecutive values which was 200). Now Days 500 and 501 are 2 consecutive "Bad Days" and the total sum is 600 which is greater than the previous 2 consecutive days on day 1 and 2. 600 will be now my max value that I need on cell L2 ( if someone change the results or values, L2 must be updated too
 

Attachments

  • calculate hard data.PNG
    calculate hard data.PNG
    19.9 KB · Views: 11

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
I'm assuming that column J is just something that you've added to the example which will not normally be there.

The easiest way would be with 2 helper columns,
In J2 and fill down =IF(AND(I2<>I3,I2="Bad Day"),SUMIFS(H$2:H2,I$2:I2,"Bad Day")-SUM(J$1:J1),"")
In K2 and fill down =IF(AND(I2<>I3,I2="Good Day"),SUMIFS(H$2:H2,I$2:I2,"Good Day")-SUM(K$1:K1),"")

Then the 'Bad day' result will be = MAX(J:J) and 'Good day' result will be =MAX(K:K)

With excel 2019, you could get it down to 1 helper column or with office 365 it might be possible to do it without helpers.
It could be done without helpers in 2016 but the formulas will be far more complex and inefficient.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
Following up on my first reply, I had a go at doing this without the helper columns. This works with the example provided, but I haven't tested it beyond that.
Book2 12-8-20.xlsm
GHIJKLM
1DaysValueResults
21100Bad DayBad DayGood Day
32100Bad Day600800
43500Good Day
545Bad Day
655Bad Day
765Bad Day
875Bad Day
985Bad Day
1095Bad Day
11105Bad Day
1211500Good Day
1312300Good Day
14500300Bad Day
15501300Bad Day
Sheet6
Cell Formulas
RangeFormula
L3:M3L3=MAX(IFNA(SUBTOTAL(9,OFFSET($H$2,AGGREGATE(15,6,(ROW($I$2:$I$15)-ROW($I$2))/($I$2:$I$15=L$2)/($I$1:$I$14<>L$2),ROW(INDIRECT("1:"&COUNTIFS($I$2:$I$15,L$2,$I$1:$I$14,"<>"&L$2)))),0,FREQUENCY(IF($I$2:$I$15=L$2,ROW($I$2:$I$15)),IF($I$2:$I$15<>L$2,IF($I$1:$I$14=L$2,ROW($I$2:$I$15)))),1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
I realised that the formulas in my earlier posts were not looking for 2 or more consecutive days and that single rows would be included in the results. I've edited the single cell formula to work with that, but haven't had chance to do the helper column one. I'll have another look at that later if you would prefer that method for simplicity.
Book2 12-8-20.xlsm
GHIJKLM
1DaysValueResults
21100Bad DayBad DayGood Day
32100Bad Day600800
431000Good Day
545Bad Day
655Bad Day
765Bad Day
875Bad Day
985Bad Day
1095Bad Day
11105Bad Day
1211500Good Day
1312300Good Day
14500300Bad Day
15501300Bad Day
Sheet6
Cell Formulas
RangeFormula
L3:M3L3=MAX(IFERROR(SUBTOTAL(9,OFFSET($H$2,AGGREGATE(15,6,(ROW($I$2:$I$15)-ROW($I$2))/($I$2:$I$15=L$2)/($I$3:$I$16=L$2)/($I$1:$I$14<>L$2),ROW(INDIRECT("1:"&COUNTIFS($I$2:$I$15,L$2,$I$3:$I$16,L$2,$I$1:$I$14,"<>"&L$2)))),0,1/(1/FREQUENCY(IF(($I$2:$I$15=L$2)*($I$1:$I$14=L$2),ROW($I$2:$I$15)),IF($I$3:$I$16<>L$2,IF($I$2:$I$15=L$2,IF($I$1:$I$14=L$2,ROW($I$2:$I$15))))))+1,1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

sama9000

New Member
Joined
Aug 11, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I realised that the formulas in my earlier posts were not looking for 2 or more consecutive days and that single rows would be included in the results. I've edited the single cell formula to work with that, but haven't had chance to do the helper column one. I'll have another look at that later if you would prefer that method for simplicity.
Book2 12-8-20.xlsm
GHIJKLM
1DaysValueResults
21100Bad DayBad DayGood Day
32100Bad Day600800
431000Good Day
545Bad Day
655Bad Day
765Bad Day
875Bad Day
985Bad Day
1095Bad Day
11105Bad Day
1211500Good Day
1312300Good Day
14500300Bad Day
15501300Bad Day
Sheet6
Cell Formulas
RangeFormula
L3:M3L3=MAX(IFERROR(SUBTOTAL(9,OFFSET($H$2,AGGREGATE(15,6,(ROW($I$2:$I$15)-ROW($I$2))/($I$2:$I$15=L$2)/($I$3:$I$16=L$2)/($I$1:$I$14<>L$2),ROW(INDIRECT("1:"&COUNTIFS($I$2:$I$15,L$2,$I$3:$I$16,L$2,$I$1:$I$14,"<>"&L$2)))),0,1/(1/FREQUENCY(IF(($I$2:$I$15=L$2)*($I$1:$I$14=L$2),ROW($I$2:$I$15)),IF($I$3:$I$16<>L$2,IF($I$2:$I$15=L$2,IF($I$1:$I$14=L$2,ROW($I$2:$I$15))))))+1,1)),""))
Press CTRL+SHIFT+ENTER to enter array formulas.
Thank you so much for your help , this gave me the solution I needed to build the formula
 

Watch MrExcel Video

Forum statistics

Threads
1,127,830
Messages
5,627,145
Members
416,223
Latest member
RichardHell

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