Sum a column until it's <= a value in a cell, return number of cells counted

[emersonp]

Hello all,

I found replies to this post, below, answered the majority of my question, but I can't get it to fully work for me:
Sum column until condition is reached - return number of cells in column counted | Chandoo.org Excel Forums - Become Awesome in Excel

I have a table of project items and cost estimates, and I am trying to return the number of projects that can be completed based on a high and low estimate. I'm trying to avoid helper columns to keep the end view clean.

I'd like to:
- Add up the amounts in COL H until it reaches the budget value in G2
- Add up the amounts in COL L until it reaches the budget value in G2
And in both cases return the number of cells added.

At present, with the forum post I've linked to I can do this for COL L, but the same formula for COL H returns #VALUE.

Any thoughts or help very gratefully received.

 

[bosco_yip]

Try,

1] In H3, enter formula :

=MATCH(G$2,MMULT(SUMIF(OFFSET(L7,,,ROW($1:$35)),">0"),1))

2] In H4, enter formula :

=MATCH(G$2,MMULT(SUMIF(OFFSET(H7,,,ROW($1:$35)),">0"),1))

 

[emersonp]

Thanks Bosco!

So I can see how the Row section is working (I think - the reference cell is ROW $1 and it sums down from there?) but could you explain the job the MMULT is doing there?

And am I right that to get the last item before the budget amount - the last affordable project - that using -1 at the end of the formula is the easiest approach?
=MATCH(G$2,MMULT(SUMIF(OFFSET(H7,,,ROW($1:$35)),">0"),1))-1

Thanks again!

Try,

1] In H3, enter formula :

=MATCH(G$2,MMULT(SUMIF(OFFSET(L7,,,ROW($1:$35)),">0"),1))

2] In H4, enter formula :

=MATCH(G$2,MMULT(SUMIF(OFFSET(H7,,,ROW($1:$35)),">0"),1))

View attachment 76110

 

[bosco_yip]

Thanks Bosco!

So I can see how the Row section is working (I think - the reference cell is ROW $1 and it sums down from there?) but could you explain the job the MMULT is doing there?

And am I right that to get the last item before the budget amount - the last affordable project - that using -1 at the end of the formula is the easiest approach?
=MATCH(G$2,MMULT(SUMIF(OFFSET(H7,,,ROW($1:$35)),">0"),1))-1

Thanks again!

1] The MMULT purpose is to force and convert the formula from array (CSE) formula into non-array formula, so you need not to press "Ctrl"+"Shift+"Enter" 3 keystrokes for array entry.

2] Yes, you are right and using "-1" is correct.

Regards

 

[Peter Bartholomew]

I looked ahead to see what Dynamic Array solutions could look like once the Lambda helper functions become generally available.

The pink cells contain formulas. Accumulations are never simple with array formulas, so I took a look at a solution using the new functions.

= LET(
   accumulated, SCAN(0,HighEstimate, LAMBDA(acc,high, SUM(acc, high))),
   runningTotal, IF(accumulated < AvailableBudget, accumulated),
   COUNT(runningTotal)
   )

The SCAN function runs through the terms 'high' of 'HighEstimate' one at a time, adding them to the running balance 'acc'. The second line imposes the cut-off and the 3rd counts the terms remaining.

 

[deciog]

Peter Bartholomew, Good Morning.

Please post a model with this SCAN formula because I want to study it, and in this example I can't assemble it, if possible always post a model because my Excel is Portuguese (Brazil) and I am a member of the Office Insider

Thank you in advance for your functions class

Hugs

Decio Gassi

 

[Peter Bartholomew]

Sorry Decio, I forgot that you might wish to see a copy. Do the function have Portuguese versions at this stage of development?

 

[deciog]

Thanks Peter, yes there is also in the Portuguese version (Brazil)

Hugs

Decio