CODEWITHSUNDEEP
arraysdategoogle-sheetssumarray-formulas
hansimglueck
hansimglueck

Reputation: 15

Google Sheet SUMIF Array formula doesnt work

in my sheet I want to calculate the days until a certain vaue is reached. I'm using this formulas for it. https://infoinspired.com/google-docs/spreadsheet/target-sum-reached-row-in-google-sheets/

For several examples, it worked without a problem, but this time I get the wrong values... :(

In my example: https://docs.google.com/spreadsheets/d/1MYYNZB94RdF21hg60-mERCy2hP48c8IhkprV-LBvZrs/edit?usp=sharing

in the tab "HERE" I want to sum the cells F2:F315 until the value 120000 (cell J9) is reached the first time. The output should be the "days" or the amount of sums of the cells.

As output I get 265 even it should be 191 or so.

=countif(ArrayFormula(sumif(row(A2:A315),"<="&row(A2:A315),F2:F315)),"<="&J9)

To double-check I made a helper column which adds the earnings (column F) in column H. According to this helper column the first time >120000 is reached is on H193

I just don't get it... maybe I have a logic error in my mind?

Upvotes: 1

Views: 284

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(DAYS(INDIRECT(ADDRESS(MIN(IF(MMULT(TRANSPOSE((ROW(F2:F)<=
 TRANSPOSE(ROW(F2:F)))*F2:F), SIGN(F2:F)^0)>J9, ROW(A2:A), )), 1)), A2))

0

UPDATE for faster speed:

=ARRAYFORMULA(DAYS(INDIRECT(ADDRESS(MIN(IF(MMULT(TRANSPOSE((ROW(
 INDIRECT("F2:F"&COUNTA(F2:F)+1))<= TRANSPOSE(ROW(
 INDIRECT("F2:F"&COUNTA(F2:F)+1))))*
 INDIRECT("F2:F"&COUNTA(F2:F)+1)), SIGN(
 INDIRECT("F2:F"&COUNTA(F2:F)+1))^0)>J9, ROW(A2:A), )), 1)), A2))

Upvotes: 0

MattKing
MattKing

Reputation: 7773

Unless I misunderstand the question, I think this fairly simple formula should do it quickly... For the actual date:

=MIN(FILTER(A2:A,SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)>=J9))

For the number of days:

=MIN(FILTER(A2:A,SUMIF(ROW(F2:F),"<="&ROW(F2:F),F2:F)>=J9))-A2+1

Upvotes: 1

Related Questions