How do I get an IF statement to pull a date range??

[Brooke Medvecky]

I've created an IF statement with multiple formulas, but I can't seem to
figure out if it is possible to pull the information by a specific date
range. I have sales reps that visit customers on specific dates and I need
my tally sheet to only pull dates within that current week. Does anyone know
if this is possible??

This is what I have so far:
=SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))

If you look above I have it to calculate the date value starting at
03-18-05, but I would like it to only pull that date through the next week.

Help???

[CLR]

Take a look at Data > Filter > AutoFilter > Custom, and select GreaterThan
and LesserThan values..............this will filter out everything except
those within your desired date range.


Vaya con Dios,
Chuck, CABGx3


"Brooke Medvecky" <Brooke [email protected]> wrote in
message news:[email protected]...
> I've created an IF statement with multiple formulas, but I can't seem to
> figure out if it is possible to pull the information by a specific date
> range. I have sales reps that visit customers on specific dates and I
need
> my tally sheet to only pull dates within that current week. Does anyone
know
> if this is possible??
>
> This is what I have so far:
> =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
> rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
>
> If you look above I have it to calculate the date value starting at
> 03-18-05, but I would like it to only pull that date through the next
week.
>
> Help???
>
>
>
>

[Brooke Medvecky]

I realize that I can Autofilter my whole workbook, but I want to eliminte
doing that hand filter stuff. I have a different worksheet that has a tally
sheet that I would like to reference instead of going into 20 sales reps
worksheets to figure out who they've seen each week.

Is it possible to write an IF statement to calculate only date ranges???
DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There
has to be a way to pull a range. Help???

"CLR" wrote:

> Take a look at Data > Filter > AutoFilter > Custom, and select GreaterThan
> and LesserThan values..............this will filter out everything except
> those within your desired date range.
>
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Brooke Medvecky" <Brooke [email protected]> wrote in
> message news:[email protected]...
> > I've created an IF statement with multiple formulas, but I can't seem to
> > figure out if it is possible to pull the information by a specific date
> > range. I have sales reps that visit customers on specific dates and I
> need
> > my tally sheet to only pull dates within that current week. Does anyone
> know
> > if this is possible??
> >
> > This is what I have so far:
> > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
> > rep!P2:P216=I7,IF(sales rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> >
> > If you look above I have it to calculate the date value starting at
> > 03-18-05, but I would like it to only pull that date through the next
> week.
> >
> > Help???
> >
> >
> >
> >
>
>
>

[CLR]

Assuming your dates are in column A, and your values in column B, then enter
a StartDate in E1 and put this formula in C1 and copy down......only those
rows within the date range of E1+6 will show up.........then sum them,
average them or whatever........
=IF(AND(A1>=$E$1,A1<=$E$1+6),B1,"")

BTW, Autofilter will run under macro control.......

hth
Vaya con Dios,
Chuck, CABGx3


"Brooke Medvecky" <[email protected]> wrote in
message news:[email protected]...
> I realize that I can Autofilter my whole workbook, but I want to eliminte
> doing that hand filter stuff. I have a different worksheet that has a
tally
> sheet that I would like to reference instead of going into 20 sales reps
> worksheets to figure out who they've seen each week.
>
> Is it possible to write an IF statement to calculate only date ranges???
> DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work. There
> has to be a way to pull a range. Help???
>
> "CLR" wrote:
>
> > Take a look at Data > Filter > AutoFilter > Custom, and select
GreaterThan
> > and LesserThan values..............this will filter out everything
except
> > those within your desired date range.
> >
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Brooke Medvecky" <Brooke [email protected]> wrote in
> > message news:[email protected]...
> > > I've created an IF statement with multiple formulas, but I can't seem
to
> > > figure out if it is possible to pull the information by a specific
date
> > > range. I have sales reps that visit customers on specific dates and I
> > need
> > > my tally sheet to only pull dates within that current week. Does
anyone
> > know
> > > if this is possible??
> > >
> > > This is what I have so far:
> > > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales rep!$AA$2:$AA$216=I4,IF(sales
> > > rep!P2:P216=I7,IF(sales
rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> > >
> > > If you look above I have it to calculate the date value starting at
> > > 03-18-05, but I would like it to only pull that date through the next
> > week.
> > >
> > > Help???
> > >
> > >
> > >
> > >
> >
> >
> >

[CLR]

or maybe...........just this one formula to give you the sum of the criteria
values......

=SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100)

Again assuming Dates in column A, values in column B, and StartDate in E1.

Vaya con Dios,
Chuck, CABGx3


"CLR" <[email protected]> wrote in message
news:[email protected]...
> Assuming your dates are in column A, and your values in column B, then
enter
> a StartDate in E1 and put this formula in C1 and copy down......only
those
> rows within the date range of E1+6 will show up.........then sum them,
> average them or whatever........
> =IF(AND(A1>=$E$1,A1<=$E$1+6),B1,"")
>
> BTW, Autofilter will run under macro control.......
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Brooke Medvecky" <[email protected]> wrote in
> message news:[email protected]...
> > I realize that I can Autofilter my whole workbook, but I want to
eliminte
> > doing that hand filter stuff. I have a different worksheet that has a
> tally
> > sheet that I would like to reference instead of going into 20 sales reps
> > worksheets to figure out who they've seen each week.
> >
> > Is it possible to write an IF statement to calculate only date ranges???
> > DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work.
There
> > has to be a way to pull a range. Help???
> >
> > "CLR" wrote:
> >
> > > Take a look at Data > Filter > AutoFilter > Custom, and select
> GreaterThan
> > > and LesserThan values..............this will filter out everything
> except
> > > those within your desired date range.
> > >
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Brooke Medvecky" <Brooke [email protected]> wrote in
> > > message news:[email protected]...
> > > > I've created an IF statement with multiple formulas, but I can't
seem
> to
> > > > figure out if it is possible to pull the information by a specific
> date
> > > > range. I have sales reps that visit customers on specific dates and
I
> > > need
> > > > my tally sheet to only pull dates within that current week. Does
> anyone
> > > know
> > > > if this is possible??
> > > >
> > > > This is what I have so far:
> > > > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales
rep!$AA$2:$AA$216=I4,IF(sales
> > > > rep!P2:P216=I7,IF(sales
> rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> > > >
> > > > If you look above I have it to calculate the date value starting at
> > > > 03-18-05, but I would like it to only pull that date through the
next
> > > week.
> > > >
> > > > Help???
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

[Brooke Medvecky]

I'm sorry that I'm not quite getting it, but I'm kinda new to these If
statements. I understand the formula, but how would I get it to fit into my
whole if statement of
=SUM(IF(salesrep!AB2:AB216=I3,IF(salesrep!AA2:AA216=I4,IF(salesrep!P2:p216=I7,IF(salesrep!N2:N216 ????? Date Range issue.

Assuming that AB = One criteria I'm pulling
Assuming that AA = Second criteria
Assuming that P = Third Criteria
Assuming that N= all the visited dates

If I put a start date in E1 then how would the formula look inside the above
instead of a single IF statement for the date ranges. I need the formula to
recognize the above criterias also.

I hope this makes sense. Thanks for your time.

"CLR" wrote:

> or maybe...........just this one formula to give you the sum of the criteria
> values......
>
> =SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100)
>
> Again assuming Dates in column A, values in column B, and StartDate in E1.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Assuming your dates are in column A, and your values in column B, then
> enter
> > a StartDate in E1 and put this formula in C1 and copy down......only
> those
> > rows within the date range of E1+6 will show up.........then sum them,
> > average them or whatever........
> > =IF(AND(A1>=$E$1,A1<=$E$1+6),B1,"")
> >
> > BTW, Autofilter will run under macro control.......
> >
> > hth
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "Brooke Medvecky" <[email protected]> wrote in
> > message news:[email protected]...
> > > I realize that I can Autofilter my whole workbook, but I want to
> eliminte
> > > doing that hand filter stuff. I have a different worksheet that has a
> > tally
> > > sheet that I would like to reference instead of going into 20 sales reps
> > > worksheets to figure out who they've seen each week.
> > >
> > > Is it possible to write an IF statement to calculate only date ranges???
> > > DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work.
> There
> > > has to be a way to pull a range. Help???
> > >
> > > "CLR" wrote:
> > >
> > > > Take a look at Data > Filter > AutoFilter > Custom, and select
> > GreaterThan
> > > > and LesserThan values..............this will filter out everything
> > except
> > > > those within your desired date range.
> > > >
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "Brooke Medvecky" <Brooke [email protected]> wrote in
> > > > message news:[email protected]...
> > > > > I've created an IF statement with multiple formulas, but I can't
> seem
> > to
> > > > > figure out if it is possible to pull the information by a specific
> > date
> > > > > range. I have sales reps that visit customers on specific dates and
> I
> > > > need
> > > > > my tally sheet to only pull dates within that current week. Does
> > anyone
> > > > know
> > > > > if this is possible??
> > > > >
> > > > > This is what I have so far:
> > > > > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales
> rep!$AA$2:$AA$216=I4,IF(sales
> > > > > rep!P2:P216=I7,IF(sales
> > rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> > > > >
> > > > > If you look above I have it to calculate the date value starting at
> > > > > 03-18-05, but I would like it to only pull that date through the
> next
> > > > week.
> > > > >
> > > > > Help???
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

[CLR]

Hi Brooke.........
This is untested, but give it a try in a helper column, Row2 and copy
down............

=IF(AND(Salesrep!AB2=$I$3,Salesrep!AA2=$I$4,SalesrepP2=$I$7),"ConditionsMatc
hCriteria","")

Then you can Copy > PasteSpecial > Values on the helper column to get rid of
the formulas, then sort on it, and the dates adjacent to the cells with the
"ConditionsMatchCriteria" string, should be the ones you're looking
for..........

hth
Vaya con Dios,
Chuck, CABGx3



"Brooke Medvecky" <[email protected]> wrote in
message news:[email protected]...
> I'm sorry that I'm not quite getting it, but I'm kinda new to these If
> statements. I understand the formula, but how would I get it to fit into
my
> whole if statement of
>
=SUM(IF(salesrep!AB2:AB216=I3,IF(salesrep!AA2:AA216=I4,IF(salesrep!P2:p216=I
7,IF(salesrep!N2:N216 ????? Date Range issue.
>
> Assuming that AB = One criteria I'm pulling
> Assuming that AA = Second criteria
> Assuming that P = Third Criteria
> Assuming that N= all the visited dates
>
> If I put a start date in E1 then how would the formula look inside the
above
> instead of a single IF statement for the date ranges. I need the formula
to
> recognize the above criterias also.
>
> I hope this makes sense. Thanks for your time.
>
> "CLR" wrote:
>
> > or maybe...........just this one formula to give you the sum of the
criteria
> > values......
> >
> > =SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100)
> >
> > Again assuming Dates in column A, values in column B, and StartDate in
E1.
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "CLR" <[email protected]> wrote in message
> > news:[email protected]...
> > > Assuming your dates are in column A, and your values in column B, then
> > enter
> > > a StartDate in E1 and put this formula in C1 and copy down......only
> > those
> > > rows within the date range of E1+6 will show up.........then sum them,
> > > average them or whatever........
> > > =IF(AND(A1>=$E$1,A1<=$E$1+6),B1,"")
> > >
> > > BTW, Autofilter will run under macro control.......
> > >
> > > hth
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "Brooke Medvecky" <[email protected]> wrote in
> > > message news:[email protected]...
> > > > I realize that I can Autofilter my whole workbook, but I want to
> > eliminte
> > > > doing that hand filter stuff. I have a different worksheet that has
a
> > > tally
> > > > sheet that I would like to reference instead of going into 20 sales
reps
> > > > worksheets to figure out who they've seen each week.
> > > >
> > > > Is it possible to write an IF statement to calculate only date
ranges???
> > > > DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work.
> > There
> > > > has to be a way to pull a range. Help???
> > > >
> > > > "CLR" wrote:
> > > >
> > > > > Take a look at Data > Filter > AutoFilter > Custom, and select
> > > GreaterThan
> > > > > and LesserThan values..............this will filter out everything
> > > except
> > > > > those within your desired date range.
> > > > >
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > >
> > > > > "Brooke Medvecky" <Brooke [email protected]>
wrote in
> > > > > message news:[email protected]...
> > > > > > I've created an IF statement with multiple formulas, but I can't
> > seem
> > > to
> > > > > > figure out if it is possible to pull the information by a
specific
> > > date
> > > > > > range. I have sales reps that visit customers on specific dates
and
> > I
> > > > > need
> > > > > > my tally sheet to only pull dates within that current week.
Does
> > > anyone
> > > > > know
> > > > > > if this is possible??
> > > > > >
> > > > > > This is what I have so far:
> > > > > > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales
> > rep!$AA$2:$AA$216=I4,IF(sales
> > > > > > rep!P2:P216=I7,IF(sales
> > > rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> > > > > >
> > > > > > If you look above I have it to calculate the date value starting
at
> > > > > > 03-18-05, but I would like it to only pull that date through the
> > next
> > > > > week.
> > > > > >
> > > > > > Help???
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

[Brooke Medvecky]

Thank you for your time, but this just isn't getting the results I need.
Unfortunately this is a weekly report that would pose to be a pain if I had
to do the criteria match every week. I was hoping to put the formula on a
seperate tally worksheet by Week 1, Week 2, etc. This way it would calcuate
the date range from Thursday to Thursday of each week. I just need it to
count how many visits they make to a particular customer. It wouldn't be
tallying dollars at this time.

So if Joe Blow has 200 customers to visit (of that) how many fall between
one given week?? Additional criteria needing to fit into IF statement is -
Of course must be between a date range, have a bucket # of 4, and have signed
contract as "yes".

Thanks Again. I will see what I can come up with.

"CLR" wrote:

> Hi Brooke.........
> This is untested, but give it a try in a helper column, Row2 and copy
> down............
>
> =IF(AND(Salesrep!AB2=$I$3,Salesrep!AA2=$I$4,SalesrepP2=$I$7),"ConditionsMatc
> hCriteria","")
>
> Then you can Copy > PasteSpecial > Values on the helper column to get rid of
> the formulas, then sort on it, and the dates adjacent to the cells with the
> "ConditionsMatchCriteria" string, should be the ones you're looking
> for..........
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Brooke Medvecky" <[email protected]> wrote in
> message news:[email protected]...
> > I'm sorry that I'm not quite getting it, but I'm kinda new to these If
> > statements. I understand the formula, but how would I get it to fit into
> my
> > whole if statement of
> >
> =SUM(IF(salesrep!AB2:AB216=I3,IF(salesrep!AA2:AA216=I4,IF(salesrep!P2:p216=I
> 7,IF(salesrep!N2:N216 ????? Date Range issue.
> >
> > Assuming that AB = One criteria I'm pulling
> > Assuming that AA = Second criteria
> > Assuming that P = Third Criteria
> > Assuming that N= all the visited dates
> >
> > If I put a start date in E1 then how would the formula look inside the
> above
> > instead of a single IF statement for the date ranges. I need the formula
> to
> > recognize the above criterias also.
> >
> > I hope this makes sense. Thanks for your time.
> >
> > "CLR" wrote:
> >
> > > or maybe...........just this one formula to give you the sum of the
> criteria
> > > values......
> > >
> > > =SUMIF(A1:A100,"<="&E1+6,B1:B100)-SUMIF(A1:A100,"<"&E1,B1:B100)
> > >
> > > Again assuming Dates in column A, values in column B, and StartDate in
> E1.
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > > "CLR" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Assuming your dates are in column A, and your values in column B, then
> > > enter
> > > > a StartDate in E1 and put this formula in C1 and copy down......only
> > > those
> > > > rows within the date range of E1+6 will show up.........then sum them,
> > > > average them or whatever........
> > > > =IF(AND(A1>=$E$1,A1<=$E$1+6),B1,"")
> > > >
> > > > BTW, Autofilter will run under macro control.......
> > > >
> > > > hth
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > > "Brooke Medvecky" <[email protected]> wrote in
> > > > message news:[email protected]...
> > > > > I realize that I can Autofilter my whole workbook, but I want to
> > > eliminte
> > > > > doing that hand filter stuff. I have a different worksheet that has
> a
> > > > tally
> > > > > sheet that I would like to reference instead of going into 20 sales
> reps
> > > > > worksheets to figure out who they've seen each week.
> > > > >
> > > > > Is it possible to write an IF statement to calculate only date
> ranges???
> > > > > DATEVALUE("3/18/05-"3/24/05") I've tried this and it doesn't work.
> > > There
> > > > > has to be a way to pull a range. Help???
> > > > >
> > > > > "CLR" wrote:
> > > > >
> > > > > > Take a look at Data > Filter > AutoFilter > Custom, and select
> > > > GreaterThan
> > > > > > and LesserThan values..............this will filter out everything
> > > > except
> > > > > > those within your desired date range.
> > > > > >
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > >
> > > > > > "Brooke Medvecky" <Brooke [email protected]>
> wrote in
> > > > > > message news:[email protected]...
> > > > > > > I've created an IF statement with multiple formulas, but I can't
> > > seem
> > > > to
> > > > > > > figure out if it is possible to pull the information by a
> specific
> > > > date
> > > > > > > range. I have sales reps that visit customers on specific dates
> and
> > > I
> > > > > > need
> > > > > > > my tally sheet to only pull dates within that current week.
> Does
> > > > anyone
> > > > > > know
> > > > > > > if this is possible??
> > > > > > >
> > > > > > > This is what I have so far:
> > > > > > > =SUM(IF(sales rep!AB2:AB216=I3,IF(sales
> > > rep!$AA$2:$AA$216=I4,IF(sales
> > > > > > > rep!P2:P216=I7,IF(sales
> > > > rep!$N$2:$N$216>=DATEVALUE("3/18/2005"),1,0)))))
> > > > > > >
> > > > > > > If you look above I have it to calculate the date value starting
> at
> > > > > > > 03-18-05, but I would like it to only pull that date through the
> > > next
> > > > > > week.
> > > > > > >
> > > > > > > Help???
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

[Browner]

If i wanted a cell to equal a value if a particular cell was within a certain range, how would i accomplish that?


For example:

Cell A1=51 and cell B2 to equal 10 if cell A1 is between 50 and 60

I can do this easily for greater then or equal to but not when combining the >50 and <60 particulars. Please help!

[Toppers]

in B2

=if(and(A1>50,A1<60),10,?)

? what if NOT true ... what is B2?

"Browner" wrote:

>
> If i wanted a cell to equal a value if a particular cell was within a
> certain range, how would i accomplish that?
>
>
> For example:
>
> Cell A1=51 and cell B2 to equal 10 if cell A1 is between 50 and 60
>
> I can do this easily for greater then or equal to but not when
> combining the >50 and <60 particulars. Please help!
>
>
> --
> Browner
> ------------------------------------------------------------------------
> Browner's Profile: http://www.excelforum.com/member.php...o&userid=33657
> View this thread: http://www.excelforum.com/showthread...hreadid=357335
>
>