Flag rows contributing to the sum criteria

Question

I have two columns like this:

Name  Value
A     1
A     4
B     3 
B     2
B     5
C     6
C     8
C     10
C     4

I am doing sumif based on Names but I have a criteria to full fill. Looking from the least values in a name group whenever my sum reaches lets say 5 I want those rows to have a flag 1 or else 0. In this example it should be:

Name  Value  Flag
    A     1   1
    A     4   1
    B     3   1
    B     2   1
    B     5   0
    C     6   0
    C     8   0
    C     10  0
    C     4   1

The data is random and not in any order and file is dynamic so can not work around by just putting it in decreasing order. I do not have any idea about offset. Could it be done without using offset and only by regular ifs, sumifs etc. Thanks a tonn!

I don't understand your expected results for the Flag column. Can you clarify one or two? For example, why are the first three values in this column for Name C 0, and the final one 1? — XOR LX
The increasing order of C values are 4,6,8,10. Now when I start summing up from the least it is 4,10,18,28 I mean cumulative. But my condition which was "5" was met only after the first so I want that as "1" and others as "0". Hope you are clear. — Abhishek Singh
What if a given Name contains one or more values which occur more than once? Which should be given precedence for flagging in terms of order of summation? For example, what if every entry in the table you give were for Name="A" and Value=1? — XOR LX
Actually my values are kind of share of a market in a decreasing / or increasing format so it is not the case at all because all values are like 4.2, 5.6, 7.8 summing up to 100 for one name. But I agree that what you mentioned is kind of deadlock — Abhishek Singh

XOR LX XOR LX · Accepted Answer · 2017-04-19T11:07:13

Perhaps this can be simplified somewhat, though, assuming you put your chosen threshold (e.g. 5) in J1 and that, as implied by your reply to my last comment, no one value for a given Name occurs more than once, then, in C2, array formula**:

=IFERROR(GESTEP(MATCH(1,0/(MMULT(0+(ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A$2:A$10,A2)))>=TRANSPOSE(ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A$2:A$10,A2))))),SMALL(IF(A$2:A$10=A2,B$2:B$10),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A$2:A$10,A2)))))<=J$1)),MATCH(B2,SMALL(IF(A$2:A$10=A2,B$2:B$10),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A$2:A$10,A2)))))),0)

Copy down as required.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Flag rows contributing to the sum criteria

2 Answers