CODEWITHSUNDEEP
excelexcel-formula
ArthurCL
ArthurCL

Reputation: 3

Find SUBTOTAL of List at Specific Interval

In Excel, I need to find which row has total sum of next nearest 100 (minimum 100), next total sum must using next value to get total sum.

As example below, because row number will be use to VLOOKUP a data series only when the sum of value equal or minimum every 100.

Row    Value    Mapping
---    ------   -------
1      1
2      50
3      52       1 @ (1+50+52=103) Row #3 in Data series return: David
4      72
5      55       2 @ (72+55=127) Row #5 in Data series return: Alex
6      70
7      80       3 @ (70+80=150) Row #7 in Data series return: Alicia

At mapping column where I will put VLOOKUP code.

Upvotes: 0

Views: 316

Answers (1)

user4039065
user4039065

Reputation:

In C2 as,

=IF(AND(SUM(INDEX(B:B, IFERROR(MATCH(1E+99, C$1:C1)+1, 1)):B2)>=100,SUM(INDEX(B:B, IFERROR(MATCH(1E+99, C$1:C1)+1, 1)):B1)<100),MAX(C$1:C1)+1,"")

        NEXT_100_TOTAL

Fill down as necessary.

Upvotes: 1

Related Questions