# this will be a fun one!

#### pdxstarflyer

##### New Member
In column "B" I have dates. In column "I" I have a number (or blank).

I'm trying to sum column "I" starting from the most recent entry (as determined by date in column "B" and is 99% of the time the last row on the sheet) and, summing column "I" backwards, return the date from column "B" where the most recent date or dates in column "I" >=3. On top of that I only want to do this when column "C" equals X

Example 1:

3/1/11 X 3
3/2/11 X 2
3/3/11 X 1
3/4/11 X 0
3/5/11 X 3
3/6/11 Y 1
3/7/11 Y 2

The formula should return the date "3/5/11" because that is the where X>=3 counting backwards from today.

Example 2:

3/1/11 X 3
3/2/11 X 2
3/3/11 X 1
3/4/11 X 0
3/5/11 X 1
3/6/11 Y 1
3/7/11 Y 2

The formula should return the date "3/2/11" because that is the where X>=3 counting backwards from today.

I'm really at a loss and have tried all the basic tricks I know. Any pointers will be much appreciated!

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### GlennUK

##### Well-known Member
Are the dates in date order? Will searching for the furthest down the sheet for X>=3 be OK?

#### T. Valko

##### Well-known Member
In column "B" I have dates. In column "I" I have a number (or blank).

I'm trying to sum column "I" starting from the most recent entry (as determined by date in column "B" and is 99% of the time the last row on the sheet) and, summing column "I" backwards, return the date from column "B" where the most recent date or dates in column "I" >=3. On top of that I only want to do this when column "C" equals X

Example 1:

3/1/11 X 3
3/2/11 X 2
3/3/11 X 1
3/4/11 X 0
3/5/11 X 3
3/6/11 Y 1
3/7/11 Y 2

The formula should return the date "3/5/11" because that is the where X>=3 counting backwards from today.

Example 2:

3/1/11 X 3
3/2/11 X 2
3/3/11 X 1
3/4/11 X 0
3/5/11 X 1
3/6/11 Y 1
3/7/11 Y 2

The formula should return the date "3/2/11" because that is the where X>=3 counting backwards from today.

I'm really at a loss and have tried all the basic tricks I know. Any pointers will be much appreciated!
I think your sample 2 result should be 3/1/11.

Try this...

=LOOKUP(2,1/((C2:C8="x")*(I2:I8>=3)),A2:A8)

Format as Date

#### pdxstarflyer

##### New Member
actually I stated I need column "I" to be summed... so I'm not just looking for the date where X>=3 in a single row. I'm looking for the date where all summed values of X are >=3 starting from the most recent date and looking backwards until the condition is met. That's why Example 2 is the way it is.

3/1/11 X 3
3/2/11 X 2
3/3/11 X 1
3/4/11 X 0
3/5/11 X 1
3/6/11 Y 1
3/7/11 Y 2

The three bolded rows summed are >=3 and I want to return the date of 3/2/11 where that argument begins.

Thanks for the responses so far! I hope this is more clear.

#### cornflakegirl

##### Well-known Member

Can you use a helper column that contains the numbers summed from the bottom? Then you could adapt Biff's formula.

#### pdxstarflyer

##### New Member
Can you use a helper column that contains the numbers summed from the bottom? Then you could adapt Biff's formula.

excellent idea! I will give that a shot later today when I can spend a few minutes on it.

#### pdxstarflyer

##### New Member
ack... still stuck. one of the problems is I'm actually using Google docs, so the FILTER formula doesn't work there. I just thought I'd see what advice I could find here and apply it there... sigh.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,087
Messages
5,835,302
Members
430,351
Latest member
ddalton

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back