Sumproduct formula to count the latest run of 1's problem

**Philb1** · 02-13-2012, 05:57 AM

Hello people.. I have a Sumproduct formula where I'm trying to have the arrays start from A10. It counts the last consecutive run of 1's. If the arrays start at A1, the formula works as it should. If it starts at A10, it returns -9. I can't figure out where to put the adjustment. Normally it would be ROW()-9, but not this time it seems.
I've posted both versions
Any ideas please
Thanks

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**e4excel** · 02-13-2012, 06:30 AM

Hi Phil,

It would be easier to understand with a Dummy Worksheet.

REgards
e4excel

**OnErrorGoto0** · 02-13-2012, 08:08 AM

I think you want:

=SUMPRODUCT(MAX((ROW(A$10:A$100)-9)*(A$10:A$100=1)))-SUMPRODUCT(MAX((ROW(A$10:INDEX(A$10:A$100,SUMPRODUCT(MAX((ROW(A$10:A$100)-9)*(A$10:A$100=1)))))-9)*(A$10:INDEX(A$10:A$100,SUMPRODUCT(MAX((ROW(A$10:A$100)-9)*(A$10:A$100=1))))="")))

**OnErrorGoto0** · 02-13-2012, 08:09 AM

Or perhaps easier to adjust yourself in future:

=SUMPRODUCT(MAX((ROW(A$10:A$100)-ROW(A$10)+1)*(A$10:A$100=1)))-SUMPRODUCT(MAX((ROW(A$10:INDEX(A$10:A$100,SUMPRODUCT(MAX((ROW(A$10:A$100)-ROW(A$10)+1)*(A$10:A$100=1)))))-ROW(A$10)+1)*(A$10:INDEX(A$10:A$100,SUMPRODUCT(MAX((ROW(A$10:A$100)-ROW(A$10)+1)*(A$10:A$100=1))))="")))

**Philb1** · 02-14-2012, 02:47 AM

Thank you Rory..
I had all the -9's in the right place, except for the third one when I was trying. I was on the right track, but not quite.
Thanks again
Phil