Find the row when a sum on a column with certain values on rows is >= of a value

[pyrgas]

A B C
----------------------------
1 INV 10 1/1/19
2 REIN 15 2/1/19
3 BO 5 3/1/19
4 REIN 40 4/1/19
5 INV 20 5/1/19
6 REIN 10 6/1/19
7 REIN 20 7/1/19
8 WD 40 8/1/19
9 INV 10 9/1/19


Hi everyone,
Please can anyone tell me how to find on which date on column C, the sum on column B is >=70 on rows that contain "REIN" in column A

Thank's in advance

Pyrgas Ioannis<!--[if gte vml 1]><v:shapetype id="_x0000_t75" coordsize="21600,21600" o:spt="75" oreferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"> <v:stroke joinstyle="miter"/> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"/> <v:f eqn="sum @0 1 0"/> <v:f eqn="sum 0 0 @1"/> <v:f eqn="prod @2 1 2"/> <v:f eqn="prod @3 21600 pixelWidth"/> <v:f eqn="prod @3 21600 pixelHeight"/> <v:f eqn="sum @0 0 1"/> <v:f eqn="prod @6 1 2"/> <v:f eqn="prod @7 21600 pixelWidth"/> <v:f eqn="sum @8 21600 0"/> <v:f eqn="prod @7 21600 pixelHeight"/> <v:f eqn="sum @10 21600 0"/> </v:formulas> <vath o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"/> <o:lock v:ext="edit" aspectratio="t"/></v:shapetype><v:shape id="_x0000_i1025" type="#_x0000_t75" style='width:171.75pt; height:199.5pt'> <v:imagedata src="file:///C:\Users\admin\AppData\Local\Temp\msohtmlclip1\01\clip_image001.jpg" o:title="Screenshot_2"/></v:shape><![endif]--><!--[if !vml]--><!--[endif]-->

 

[Gerald Higgins]

Hi, just to be clear, what should the answer be ?

Is it 7/1/19 from row 7 ?

 

[pyrgas]

Hi, just to be clear, what should the answer be ?

Is it 7/1/19 from row 7 ?

Thanks for the quick response.
Exactly because


sum
================
1
2 REIN 15 2/1/19 15
3
4 REIN 40 4/1/19 45
5
6 REIN 10 6/1/19 55
7 REIN 20 7/1/19 75 where is >=70
8
9

 

[Gerald Higgins]

OK so one way is to use helper columns like this.

Let's assume your target value of 70 is stored in cell F1.

In D1
=IF(A1="REIN",B1,0)

In D2
=IF(A2="REIN",D1+B2,D1)

Copy the D2 formula all the way down column D.

In E1
=IF(D1>=F$1,"YES","")

In E2
=IF(AND(D2>=F$1,COUNTIF(E$1:E1,"YES")=0),"YES","")

Copy the E2 formula all the way down column E.

This puts a "YES" on the row where the target value is met.

You can then use this to return the relevant date to another cell containing a formula like this
=INDEX(C1:C9,MATCH("YES",E1:E9,0))

There are other ways of doing this, and some of them might be better than this

 

[pyrgas]

OK so one way is to use helper columns like this.

Let's assume your target value of 70 is stored in cell F1.

In D1
=IF(A1="REIN",B1,0)

In D2
=IF(A2="REIN",D1+B2,D1)

Copy the D2 formula all the way down column D.

In E1
=IF(D1>=F$1,"YES","")

In E2
=IF(AND(D2>=F$1,COUNTIF(E$1:E1,"YES")=0),"YES","")

Copy the E2 formula all the way down column E.

This puts a "YES" on the row where the target value is met.

You can then use this to return the relevant date to another cell containing a formula like this
=INDEX(C1:C9,MATCH("YES",E1:E9,0))

There are other ways of doing this, and some of them might be better than this

Thank's for the answer but imagin that column A is the code of an investor who is on another sheet and i want to find for every investor the date that his investment is >= 70

 

[Gerald Higgins]

Ah, additional requirements

Well, you could use additional helper columns for each investor.

 

[XOR LX]

Hi,

Assuming that the data as you give it is in Sheet1!A1:C9 and that you put your first investor of interest, e.g. "REIN", in Sheet2!A1:

In Sheet2!B1, array formula**:

=INDEX(Sheet1!C:C,SMALL(IF(Sheet1!A$1:A$9=A1,ROW(Sheet1!A$1:A$9)),MATCH(1,GESTEP(MMULT(GESTEP(ROW(INDIRECT("1:"&COUNTIF(Sheet1!A$1:A$9,A1))),TRANSPOSE(ROW(INDIRECT("1:"&COUNTIF(Sheet1!A$1:A$9,A1))))),INDEX(Sheet1!B:B,N(IF(1,MODE.MULT(IF(Sheet1!A$1:A$9=A1,ROW(Sheet1!A$1:A$9)*{1,1})))))),70),0)))

Copy down to give similar results for investors in A2, A3, etc.

Amend the hard-coded threshold value (70) within the above as desired (or, better still, place this value within an actual cell and reference that instead).

#N/A will result if the threshold value is never attained for a given investor. Wrap in IFERROR if desired.

Obviously you can amend the end row reference of 9 within the above to suit your requirements, though you should be careful not to make it too arbitrarily large (and certainly don't reference entire columns!), since, for each additional cell referenced, extra calculation will be required (array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not).

Regards


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

 

[XOR LX]

N.B. If you aren't using an English-language version of Excel then, in addition to translating the formula provided, you may well require a different separator within the array constant {1,1} (regrettably, online formula translators tend to exclude such details).

Regards