Forum Discussion
Solved
Calculation of values based on the next occurrence of a criteria
I am looking to create a formula to sum an amount between 2 rows based on the next occurrence if a criteria. in the below example. the criteria will be to look for "apple". my values are located in...
=IF(A2="Apple",SUMIFS($B$2:$B$32,$D$2:$D$32,D2),"")This formula returns the result in column E. There is a helper formula in column D that returns the helper results shown in the screenshot.
As variant
=LET(
amount, $A$2:$A$32,
values, $B$2:$B$32,
stop, "apple",
size, ROWS(amount),
id, SEQUENCE(size),
result, MAP( id, LAMBDA(j,
IF( INDEX(amount, j) = stop,
LET(
next, XMATCH(stop, DROP(amount, j) ),
window, SEQUENCE( IFNA( next, size - j + 1 ), , j),
SUM( INDEX( values, window ) )
),
"" )
) ),
result
)
