Sumproduct & Column

It's doing a progressive sum across the range and testing that each sum is less than or equal to cell F37.

Here's an example using a smaller range.

Let's assume F37 = 20. I'll hardcode this value into the formula.

Data Range

	AG	AH	AI	AJ
34	3
35
36
37	2	5	7	9
38
39	-----

We get the progressive sums of:

2
2+5 = 7
2+5+7 = 14
2+5+7+9 = 23

2, 7, 14, 23

This formula counts how many of those sums are <=20:

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(AG37:AJ37,,,,COLUMN(AG37:AJ37)-COLUMN(AG37)+1))<=20))

Result = 3

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Are you sure you want to do that?

COLUMN(AG37:AJ37)-COLUMN(AG37)+1

Generates the array {1,2,3,4}.

This is used to determine how many cells to progressively add.

1 = sum AG37
2 = sum AG37:AH37
3 = sum AG37:AI37
4 = sum AG37:AJ37