this will be a fun one!

pdxstarflyer

New Member
Joined
Jan 12, 2010
Messages
35
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
Joined
Jul 8, 2002
Messages
11,687
Are the dates in date order? Will searching for the furthest down the sheet for X>=3 be OK?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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
Joined
Jan 12, 2010
Messages
35
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
Joined
Nov 4, 2004
Messages
2,023

ADVERTISEMENT

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

pdxstarflyer

New Member
Joined
Jan 12, 2010
Messages
35
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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?

Disable AdBlock

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
Top