Lookup Formula Required

[Drewmyster]

Hi there,

I'm not sure where to begin with composing this formula.

What I need it to do it lookup against a date range, and to check all data from that date and aferwards, that falls above or below a certain criteria, in order to check the age of work completed, whether it falls above 10, 20 or 30 days.

For example; I have a list of dates in column A

Column A
Date
01/06/2011
02/06/2011
03/06/2011
04/06/2011
05/06/2011

Column B
Age
20 Days
16 Days
35 Days
47 Days
24 Days

How can I use a formula to filter out the ones I want? I imagine it will involve using the ">" and "<" charectors.

Thanks

 

[Expiry]

You could use Sumproduct. It would work better, though, if you simply entered a number for the days, rather than a text string like "20 Days". As text, Excel doesn't know that "21 Days" is more than "20 Days".

 

[Drewmyster]

You could use Sumproduct. It would work better, though, if you simply entered a number for the days, rather than a text string like "20 Days". As text, Excel doesn't know that "21 Days" is more than "20 Days".

Ok I can change the format of the data to just numerical with no text.

Please can you show me how to use SUMPRODUCT for what I'm trying to do.

 

[Drewmyster]

Would it require the IF formula?

For example;

IF DATE IS AFTER JUNE 30TH 2011, COUNT TOTAL OF RESULTS OVER 30?

Any help appreciated.

 

[villy]

Try...

=If (and(Day(A1)>30,month(A1)=6),countif(A$1:A$10,">30"),*FALSE condition)

 

[villy]

Just disregard my previous post and Try this...
If(A1>DATEVALUE("6/30/2011"),Countif(A1:A10,">30"),*FALSE condition)

 

[Drewmyster]

Just disregard my previous post and Try this...
If(A1>DATEVALUE("6/30/2011"),Countif(A1:A10,">30"),*FALSE condition)

I'm sorry Villy but I still can't get it right, it appears the end of the formula from ",*FALSE condition) is causing errors.

Could you test and try again for me please.

Thanks.

 

[villy]

What do you want to do if the date is not greater than June 30th 2011 then replace it in *FALSE condition...
You migth misunderstood my formula..

ex.
If(A1>DATEVALUE("6/30/2011"),Countif(A1:A10,">30"),"Date is before June 30th 2011")
some thing like this?

 

[Drewmyster]

Ah I see I think I've got it now.

Thanks for your help.

 

[villy]

Ah I see I think I've got it now.

Thanks for your help.

Hope it is clear now to you...
Thanks accepted..
You're welcome!