Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Brooke Medvecky
 
Posts: n/a
Default How do I get an IF statement to pull a date range??

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??? :)




  #3   Report Post  
Brooke Medvecky
 
Posts: n/a
Default

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 wrote in
message ...
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??? :)







  #4   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in
message ...
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 wrote in
message ...
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??? :)









  #5   Report Post  
CLR
 
Posts: n/a
Default

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" wrote in message
...
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" wrote in
message ...
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 wrote in
message ...
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??? :)













  #6   Report Post  
Brooke Medvecky
 
Posts: n/a
Default

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:AA21 6=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" wrote in message
...
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" wrote in
message ...
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 wrote in
message ...
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??? :)












  #7   Report Post  
CLR
 
Posts: n/a
Default

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,Salesr epP2=$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" wrote in
message ...
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:AA21 6=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" wrote in message
...
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" wrote in
message ...
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

wrote in
message ...
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??? :)














  #8   Report Post  
Brooke Medvecky
 
Posts: n/a
Default

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,Salesr epP2=$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" wrote in
message ...
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:AA21 6=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" wrote in message
...
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" wrote in
message ...
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

wrote in
message ...
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??? :)















  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Browner
 
Posts: n/a
Default How do I get an IF statement to pull a date range??


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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Toppers
 
Posts: n/a
Default How do I get an IF statement to pull a date range??

in B2

=if(and(A150,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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Date in an IF statement LyndieBee Excel Worksheet Functions 2 March 8th 05 05:11 PM
Completion Percentage of a date range Brian Excel Discussion (Misc queries) 4 March 4th 05 06:49 PM
How do I set a date range for conditional formatting in a macro? billo Excel Worksheet Functions 3 February 7th 05 07:19 PM
SUMPRODUCT with date range question Rob V Excel Discussion (Misc queries) 1 January 31st 05 04:55 PM
How can I sum information in a list with a date range? Dave Excel Worksheet Functions 2 November 23rd 04 09:17 PM


All times are GMT +1. The time now is 12:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"