Summing Values after Finding Max Consecutive Values

**pipsturbo** · 05-11-2009, 09:55 PM

Hi,

How are you?

If you can please help me, I'd be very appreciative.

I have 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."

This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.

1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.

2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.

I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...

The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.

If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error; lol.

Thanks,
Mike

**zbor** · 05-12-2009, 02:02 AM

1) =SUMPRODUCT(E5:E17;--(J5:J17=1))

2) =SUMPRODUCT(E5:E17;--(J5:J17=1);--(B5:B17=C1)) where C1 is entered desired date

Also, replace ; with , if you need

**pipsturbo** · 05-12-2009, 01:17 PM

Hi Zbor,

Thanks for your response.

2) works well but I need to find the Max Size for consecutive values of 1 values in J.

The problem is that 1) returns the sum of all the E column when only the criteria 1 has been met.

What I need though, is the sum Only If there has been consecutive values of 1 in the J column. Example:

If there are 12 rows & there are 8 instances in which consecutive 1 values are shown in the J column, I need to sum the Size from E column only in that instance.

Size Position
Row 1 | 0 | 0
Row 2 | 0 | 0
Row 3 | 3.3 | 1
Row 4 | 3.3 | 1
Row 5 | 6.6 | 1
Row 6 | 13.2 | 1
Row 7 | 3.3 | 1
Row 8 | 3.3 | 1
Row 9 | 6.6 | 1
Row 10 | 3.3 | 1
Row 11 | 0 | 0
Row 12 | 0 | 0

So, in this case we would only sum Rows 3-10. Now if 8 were the max consecutive value of 1s in the position column, we would have a Max position size of 42.9 in Column E.

I'm guessing that I need an array formula to properly calculate both 1) & 2).

Do you have a solution to this?

Thanks Zbor

**zbor** · 05-12-2009, 01:23 PM

Instead of --(J5:J17=1) you can use any value by defining it somewhere else: --(J5:J17=C1)

For more answers you'll need to uplaod somekind example 'cause it's hard to find soluting tapping in the dark