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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Are the dates in date order? Will searching for the furthest down the sheet for X>=3 be OK?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Can you use a helper column that contains the numbers summed from the bottom? Then you could adapt Biff's formula.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top