this will be a fun one!

[pdxstarflyer]

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!

 

[GlennUK]

Are the dates in date order? Will searching for the furthest down the sheet for X>=3 be OK?

 

[T. Valko]

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]

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]

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

 

[pdxstarflyer]

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]

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.