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 a table.
- between the 1 occurrence and next there is no amount values, hence the sum value needs to be 0.
- then between 2nd and 3rd occurrence the formula needs to sum all amount values for a,b,c,d,e.
- then between 3rd and 4th occurrence the formula needs to sum all amounts for a,b,c,d,e,f,g,h
this continues down
- for the last sum 'block' it needs to sum the values from the last occurrence of "apple' to the end of the table
| Amount | sum value | |
| apple | 0 | |
| apple | 25 | |
| a | 5 | |
| b | 3 | |
| c | 2 | |
| d | 10 | |
| e | 5 | |
| apple | 53 | |
| a | 2 | |
| b | 1 | |
| c | 4 | |
| d | 5 | |
| PLUM | ||
| e | 10 | |
| f | 6 | |
| g | 15 | |
| h | 10 | |
| Apple | 25 | |
| a | 2 | |
| b | 4 | |
| c | 7 | |
| d | 9 | |
| e | 2 | |
| f | 1 | |
| apple | 19 | |
| a | 5 | |
| b | 8 | |
| c | 3 | |
| d | 1 | |
| e | 2 | |
| f | 7 |
=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 )Thanks Oliver this works perfectly
=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.
