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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,518
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,495
Messages
5,596,490
Members
414,070
Latest member
DuncanLucas

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