Bernie Deitrick

[Trying to excel in life but need help]

Hi Bernie,

Thanks for the help.

I had a problem with the formula you gave and outlined it in the original
thread "Sumproduct help needed".

I played around with it and changed it to the following;

=SUM(OFFSET($B7:$B8,MAX(-(ROW($B7:$B14)-1),-(K$6-1)),0,MIN(ROW($B7),K$6),1))>K$5

This works but I don't really understand why. I stumbled onto the changes.
Your thoughts on this would be appreciated. I haven't worked with OFFSET,
ROW, or MAX, MIN functions before.

Again, Thank you so much for your time and expertise. It is really
appreciated.

Martin

 

[Bernie Deitrick]

Martin,

Your formula:
=SUM(OFFSET($B7:$B8,MAX(-(ROW($B7:$B14)-1),-(K$6-1)),0,MIN(ROW($B7),K$6),1))

K$5

Doesn't really need the extended ranges, since it isn't an array formula.

So it should be:
=SUM(OFFSET($B7,MAX(-(ROW($B7)-1),-(K$6-1)),0,MIN(ROW($B7),K$6),1))>K$5
My Formula:
=SUM(OFFSET($B3,MAX(-(ROW($B3)-1),-(C$2-1)),0,MIN(ROW($B3),C$2),1))>C$1
so it is essentially the same, with you having moved the control values to
other columns and shifted everything down a bit.

I guess you had problems adjusting the formula due to having moved things
around your sheet, but as long as it works....

HTH,
Bernie
MS Excel MVP

"Trying to excel in life but need help"