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!
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).